CREATE OR REPLACE PROCEDURE FMIS9999.PR_ACC_ONE_BILL_IN(I_YEAR IN VARCHAR2,I_BILLID IN VARCHAR2)
AUTHID DEFINER
AS
--功能说明:凭证实时汇总接口
--编写时间:2022-04-06
--编写人: XBF
--参数说明:
-- I_YEAR  凭证所在年份;
-- I_BILLID 汇总的凭证ID(可传多个凭证);
--变更说明:
V_I              NUMBER(10);
V_J              NUMBER(10);
V_JL             NUMBER(10);
V_IS_EXISTS	   NUMBER(10);
V_SQL            VARCHAR2(30000);
V_COMPID         VARCHAR2(8);
V_ERRORNUM       NUMBER(10);         --错误号
V_ERROR_NUM      VARCHAR2(10);
V_ERRORMSG       VARCHAR2(1000);     --错误信息
V_DETAIL_BACIUP  VARCHAR2(30);
V_DIM_BACIUP     VARCHAR2(30);
V_YEAR           NUMBER(4);
V_BOOKID         NUMBER(5);
V_ACCRANGEID     NUMBER(10);
V_SCENEID        NUMBER(10);
V_COMPID_TOP     VARCHAR2(8);
V_ACCOUNTID      NUMBER(8);
V_NOW_ACCID	   NUMBER(8);
VD_BEGIN_TIME    DATE           DEFAULT SYSDATE;
VD_END_TIME      DATE;
VC_PROGRAM_NAME  VARCHAR2(30):='PR_ACC_ONE_BILL_IN' ;
VC_CURRENT_SCHEMA     VARCHAR2(30)   DEFAULT SYS_CONTEXT('USERENV','CURRENT_SCHEMA');
V_OWNER_COMP     NUMBER(10);
V_DCCODE		   VARCHAR2(30);
V_MANAGECOMPID   VARCHAR2(80);
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE GT_ACC_BAL_SUM ';
EXECUTE IMMEDIATE 'TRUNCATE TABLE GT_ACC_DIM_SUM ';
EXECUTE IMMEDIATE 'TRUNCATE TABLE GT_ACC_BILL_DETAIL ';
EXECUTE IMMEDIATE 'TRUNCATE TABLE GT_ACC_BILL_DIM ';
EXECUTE IMMEDIATE 'TRUNCATE TABLE GT_HZ_COMPANYINFO_SES';
EXECUTE IMMEDIATE 'TRUNCATE TABLE HZBB_ZHCX_GLDX';
EXECUTE IMMEDIATE 'TRUNCATE TABLE GT_ACC_DIM_SUM_EXT';
EXECUTE IMMEDIATE 'TRUNCATE TABLE GT_GENERAL_SES1';
EXECUTE IMMEDIATE 'TRUNCATE TABLE GT_GLDXIDMC';
EXECUTE IMMEDIATE 'TRUNCATE TABLE GT_CHECK_XTGLDX';
EXECUTE IMMEDIATE 'TRUNCATE TABLE GT_BILLFZMX';
V_YEAR := TO_NUMBER(I_YEAR);
/*策略失效 */
VPD_PKG.SET_VPD_STATE_CANCEL();
/*凭证明细备份表*/
V_DETAIL_BACIUP := 'ACC_VOU_DTL_BACK'||I_YEAR ;
/*凭证维度备份表*/
V_DIM_BACIUP := 'ACC_VOU_DTL_DER_DIM_BACK'||I_YEAR ;
/*获取顶层单位*/
SELECT CSZ INTO V_COMPID_TOP FROM XTCS WHERE CSMC = 'COMPID_TOP' ;
/*获取DCCODE*/
SELECT CSZ INTO V_DCCODE FROM XTCS WHERE CSMC = 'DCCODE' ;
V_SQL := 'SELECT COUNT(*)  FROM ACC_VOUCH'||I_YEAR||' WHERE BILL_ID IN ('||I_BILLID||')' ;
EXECUTE IMMEDIATE V_SQL INTO V_I ;
-- 删除未记账凭证，则需要从备份表取数
V_SQL := 'SELECT COUNT(*)  FROM '||V_DETAIL_BACIUP||' WHERE BILL_ID IN ('||I_BILLID||')' ;
EXECUTE IMMEDIATE V_SQL INTO V_J ;
IF V_I+V_J = 0 THEN
INSERT INTO HZ_LOG
(LOG_NO ,USER_NAME ,BEGIN_TIME ,END_TIME ,LOG_LEVEL ,LOG_TYPE ,LOG_DETAIL ,IP_ADDRESS ,TIMES ,TIME_DD,SQL_TEXT)
VALUES (HZ_SEQ.NEXTVAL,VC_CURRENT_SCHEMA,VD_BEGIN_TIME,VD_END_TIME,'INFO', VC_PROGRAM_NAME
,'凭证增量汇总成功，凭证('||I_BILLID||')不存在'||DBMS_UTILITY.FORMAT_ERROR_STACK
,SYS_CONTEXT('USERENV','IP_ADDRESS'),ROUND((VD_END_TIME-VD_BEGIN_TIME)*86400,2), 	  TO_NUMBER(TO_CHAR(VD_BEGIN_TIME,'DD')),V_SQL);
COMMIT;
RETURN;
END IF ;
/*获取BOOKID,COMPID  传入凭证需都为同一个单位的,*/
IF V_I > 0 THEN
V_SQL := 'SELECT MAX(BOOKID),MAX(COMPID) FROM ACC_VOUCH'||I_YEAR||' WHERE BILLID IN ('||I_BILLID||') AND ROWNUM <= 2' ;
EXECUTE IMMEDIATE V_SQL INTO V_BOOKID,V_COMPID ;
ELSIF V_J > 0 THEN
V_SQL := 'SELECT MAX(BOOK_ID),MAX(COMP_ID) FROM '||V_DETAIL_BACIUP||' WHERE BILL_ID IN ('||I_BILLID||') AND ROWNUM <= 2' ;
EXECUTE IMMEDIATE V_SQL INTO V_BOOKID,V_COMPID ;
END IF;
/*获取发生凭证单位的所属管理范围*/
SELECT OWNER_MAN_COMP_ID INTO V_MANAGECOMPID FROM COMPANYINFO WHERE VPD_YEAR=V_YEAR AND COMPID=V_COMPID AND ACCBOOKID=V_BOOKID ;
/*获取场景ID,账期方案*/
SELECT MAX(ORG_SCE_ID),MAX(ACC_RANGE_ID) INTO V_SCENEID, V_ACCRANGEID
FROM ACC_BOOK WHERE ACC_BOOK=V_BOOKID ;
/*获取上一年最后一个账期*/
SELECT MAX(ACC_PERIOD) INTO V_ACCOUNTID FROM ACC_PERIOD WHERE ACC_YEAR=V_YEAR-1 AND ACC_RANGE_ID=V_ACCRANGEID ;
/*获取当前账期*/
SELECT MAX(ACC_PERIOD) INTO V_NOW_ACCID  FROM ACC_PERIOD WHERE ACC_YEAR=YEAR(SYSDATE) AND SYSDATE BETWEEN ACC_PD_STA_DAT AND ACC_PD_END_DAT ;
/*基础组织表中判断BITAND(ORGATTR,4)大于0的个数,大于1就是多管理范围*/
SELECT COUNT(*) INTO V_OWNER_COMP FROM (
SELECT COMPID,PCOMPID,ORGATTR FROM (
SELECT COMPID,PCOMPID,ORGATTR  FROM BASIC_ORGANIZATION WHERE DCCODE=V_DCCODE )
CONNECT BY PRIOR COMPID = PCOMPID
START WITH COMPID=V_COMPID_TOP )
WHERE BITAND(ORGATTR,4)>0 ;
/*获取凭证ID,单位ID,账期*/
V_SQL:='INSERT INTO GT_BILLFZMX(SEQID,BILLID1,KMID)
SELECT B.VPD_COMPID,B.BILLID,C.ACCOUNT_ID
FROM  XTBILL'||V_YEAR||' B,T_ZW_ACCOUNT_RANGE C
WHERE B.RANGED=C.RANGED AND C.ACCOUNT_YEAR='||V_YEAR||'
AND B.TTIME <= LAST_DAY(SYSDATE)+1
AND B.TTIME BETWEEN C.BEGIN_DATE AND C.END_DATE
AND B.BILLID IN ('||I_BILLID||') AND C.ACCRANGEID='||V_ACCRANGEID ;
EXECSQL(V_SQL,'', VC_PROGRAM_NAME||':获取凭证ID,单位ID,账期') ;
/*1. 提取需处理结零的科目所有下级到临时表 HZBB_ZHCX_GLDX中,需结转清零的科目下一年度的期初值为0   */
V_SQL:='INSERT INTO HZBB_ZHCX_GLDX ( DWDH,GLDXID,ITEMLEVEL,DXID1/*BOOKID*/ )
SELECT A.COMPID,B.IID,A.STATUS,NVL(A.BOOKID,0)
FROM T_ZW_QMKMMX_TOZERO A,T_ZWITEMSYW_EXT B
WHERE NVL(A.BOOKID,0)=B.BOOKID AND B.BOOKID=0 AND B.COMPID = '''||V_MANAGECOMPID||''' AND A.TYEAR='||V_YEAR||'-1
AND SUBSTR(A.ITEMCODE,1,4)=SUBSTR(B.CODE,1,4) AND INSTR(B.CODE,A.ITEMCODE)=1 AND A.COMPID='''||V_COMPID||'''
AND B.TYEAR='||V_YEAR||'-1 GROUP BY A.COMPID,B.IID,A.STATUS,A.BOOKID ' ;
EXECSQL(V_SQL,'', VC_PROGRAM_NAME||':1.写入结转清零科目');
/*2.1退旧  */
/*2.1.1 根据输入的凭证号，从凭证备份表中提取凭证，发生数要乘-1（退旧）到汇总中间表(凭证明细中间表)，清除对应凭证号的凭证备份表与凭证维度备份表。*/
V_SQL := 'SELECT COUNT(*) FROM '||V_DETAIL_BACIUP||' X,GT_BILLFZMX Y
WHERE X.COMP_ID=Y.SEQID AND X.BILL_ID=Y.BILLID1 AND X.ACC_PD_ID=Y.KMID ' ;
EXECUTE IMMEDIATE V_SQL INTO V_I ;
IF V_I >0 THEN
V_SQL := 'INSERT INTO GT_ACC_BILL_DETAIL( BOOK_ID,COMP_ID,SUBJECT_ID,ACC_PD_ID,CERT_DATE,BILL_ID,LOAN_SIGN,REV_MAR,STA_CUR,FOR_CUR
,BILL_ID1,ADJ_PER_MARK,ACCO_STATUS,FOR_CY_AMT,STD_CUR_AMT,QUANTITY )
SELECT X.BOOK_ID,X.COMP_ID,X.SUBJECT_ID,X.ACC_PD_ID,X.CERT_DATE,X.BILL_ID,X.LOAN_SIGN
,X.REV_MAR,X.STA_CUR,X.FOR_CUR,X.BILL_ID1,X.ADJ_PER_MARK
,X.ACCO_STATUS,-1*X.FOR_CY_AMT,-1*X.STD_CUR_AMT,X.QUANTITY
FROM '||V_DETAIL_BACIUP||' X,GT_BILLFZMX Y WHERE X.COMP_ID=Y.SEQID AND X.BILL_ID=Y.BILLID1
AND X.ACC_PD_ID=Y.KMID ' ;
EXECSQL(V_SQL,'', VC_PROGRAM_NAME||':2 写入退旧数据');
V_SQL := 'DELETE FROM '||V_DETAIL_BACIUP||' WHERE (COMP_ID,BILL_ID,ACC_PD_ID)
IN (SELECT SEQID,BILLID1,KMID FROM GT_BILLFZMX )' ;
EXECSQL(V_SQL,'', VC_PROGRAM_NAME||':3 删除凭证备份表数据');
END IF;
V_SQL := 'SELECT COUNT(*) FROM '||V_DIM_BACIUP||' X,GT_BILLFZMX Y
WHERE X.COMP_ID=Y.SEQID AND X.BILL_ID=Y.BILLID1 AND X.ACC_PD_ID=Y.KMID ' ;
EXECUTE IMMEDIATE V_SQL INTO V_I ;
IF V_I >0 THEN
V_SQL := 'INSERT INTO GT_ACC_BILL_DIM( BOOK_ID,BILL_ID,SUBJECT_ID,ACC_PD_ID,BILL_ID1,BILL_ID2,COMP_ID,LOAN_SIGN,REV_MAR,DIM_GROUP'||
',STA_CUR,FOR_CUR,STD_CUR_AMT,FOR_CY_AMT,ADJ_PER_MARK,QUANTITY,CERT_DATE,ACCO_STATUS )'||
' SELECT X.BOOK_ID,X.BILL_ID,X.SUBJECT_ID,X.ACC_PD_ID,X.BILL_ID1,X.BILL_ID2
,X.COMP_ID,X.LOAN_SIGN,X.REV_MAR,X.DIM_GROUP,X.STA_CUR,X.FOR_CUR
,-1*X.STD_CUR_AMT,-1*X.FOR_CY_AMT,X.ADJ_PER_MARK,X.QUANTITY,X.CERT_DATE,X.ACCO_STATUS
FROM '||V_DIM_BACIUP||' X,GT_BILLFZMX Y
WHERE X.COMP_ID=Y.SEQID AND X.BILL_ID=Y.BILLID1 AND X.ACC_PD_ID=Y.KMID ' ;
EXECSQL(V_SQL,'', VC_PROGRAM_NAME||':4 写入退旧数据');
V_SQL := 'DELETE FROM '||V_DIM_BACIUP||' WHERE (COMP_ID,BILL_ID,ACC_PD_ID)
IN (SELECT SEQID,BILLID1,KMID FROM GT_BILLFZMX ) ' ;
EXECSQL(V_SQL,'', VC_PROGRAM_NAME||':5 删除凭证备份表数据');
END IF;
/*2.2 加新 */
/*2.2.1 根据输入的凭证号，将凭证主表(XTBILL[YEAR])与明细表(XTBILLMX[YEAR])关联,且不再结转清零范围内，核心属性写入凭证备份表； CXBZ=1时，金额乘以-1 */
V_SQL := 'INSERT INTO '||V_DETAIL_BACIUP||'(
BOOK_ID		'|| -- 账簿ID
'	,COMP_ID		'||	-- 核算主体ID
'	,SUBJECT_ID		'||	-- 会计科目
'	,ACC_PD_ID		'||	-- 会计账期
'	,CERT_DATE		'||	-- 制证日期
'	,BILL_ID		'||	-- 凭证ID
'	,BILL_ID1		'||	-- 凭证分录id
'	,LOAN_SIGN		'||	-- 借贷标志
'	,REV_MAR		'||	-- 会计凭证的冲销标志，0:未冲销1:冲销
'	,STA_CUR		'||	-- 本位币币种，对应币种的代码
'	,FOR_CUR		'||	-- 外币币种
'	,ADJ_PER_MARK	'||	-- 调整期标志
'	,ACCO_STATUS	'||	-- 记账状态
'	,FOR_CY_AMT		'||	-- 外币金额
'	,STD_CUR_AMT	'||	-- 本位币金额
'	,QUANTITY)		'||	-- 数量
' SELECT B.BOOKID,B.VPD_COMPID,A.KMID,C.ACCOUNT_ID,B.TTIME,A.BILLID,A.BILLID1,A.BZJD,NVL(B.BZRED,0),NVL(A.MONEY1,-1),NVL(A.MONEY2,-1)
,NVL(B.RANGED,0),NVL(B.ZWHZBZ,0)
,NVL(A.TWBMONEYF,0)
,CASE A.CXBZ WHEN 1 THEN -1*NVL(A.TMONEYF,0) ELSE NVL(A.TMONEYF,0) END AS TMONEYF
,A.TNUM1F
FROM  XTBILLMX'||I_YEAR||' A,XTBILL'||I_YEAR||' B,T_ZW_ACCOUNT_RANGE C
WHERE A.BILLID=B.BILLID  AND B.RANGED=C.RANGED AND C.ACCOUNT_YEAR='||V_YEAR||'
AND B.TTIME <= LAST_DAY(SYSDATE)+1
AND B.TTIME BETWEEN C.BEGIN_DATE AND C.END_DATE
AND B.BILLID IN ('||I_BILLID||') AND C.ACCRANGEID='||V_ACCRANGEID ;
EXECSQL(V_SQL,'', VC_PROGRAM_NAME||':6 加新');
/*2.2.3 根据输入的凭证号，将凭证主表与明细表、辅助表关联，调用函数构造维度组合，写入维度备份表。注意都要生成帐期ID。*/
V_SQL :='INSERT INTO '||V_DIM_BACIUP||'(
BOOK_ID		'|| -- 账簿ID
',BILL_ID		'||	-- 凭证ID
',BILL_ID1		'||	-- 凭证分录id
',BILL_ID2		'||	-- 凭证维度分录id
',SUBJECT_ID	'||	-- 会计科目
',COMP_ID		'||	-- 核算主体ID
',LOAN_SIGN		'||	-- 借贷标志
',REV_MAR		'||	-- 会计凭证的冲销标志，0:未冲销1:冲销
',DIM_GROUP		'||	-- 维度组合
',ACC_PD_ID		'||	-- 会计期间
',STA_CUR		'||	-- 本位币币种
',FOR_CUR		'||	-- 外币币种
',STD_CUR_AMT	'||	-- 本位币金额
',FOR_CY_AMT	'||	-- 外币金额
',ADJ_PER_MARK	'||	-- 调整期标志
',QUANTITY		'||	-- 数量
',CERT_DATE		'||	-- 制证日期
',ACCO_STATUS)	'||	-- 记账状态
' SELECT  BOOK_ID,BILL_ID,BILL_ID1,BILL_ID2,SUBJECT_ID,COMP_ID,LOAN_SIGN,REV_MAR,DIM_GROUP,ACC_PD_ID
,STA_CUR,FOR_CUR,STD_CUR_AMT,FOR_CY_AMT,ADJ_PER_MARK,QUANTITY ,CERT_DATE,ACCO_STATUS
FROM (
SELECT B.BOOKID AS BOOK_ID	,NVL(A.BILLID,-1) AS BILL_ID	,NVL(A.BILLID1,-1) AS BILL_ID1	,NVL(D.BILLID2,-1) AS BILL_ID2
,A.KMID AS SUBJECT_ID	,B.VPD_COMPID AS COMP_ID	,A.BZJD AS LOAN_SIGN	,NVL(B.BZRED,0) AS REV_MAR
,FU_ACC_HZ_DIMENSION_GROUP(D.FZDXID1,D.FZDXID2,D.FZDXID3,D.FZDXID4,D.FZDXID5,A.GLDXID,NULL,NULL,NULL,NULL
,D.FLTX1,D.FLTX2,D.FLTX3,NULL,NULL,NULL, NULL,NULL,NULL,NULL) AS DIM_GROUP
,C.ACCOUNT_ID AS ACC_PD_ID
,NVL(A.MONEY1,-1) AS STA_CUR	,NVL(A.MONEY2,-1) AS FOR_CUR
,CASE A.CXBZ WHEN 1 THEN -1*NVL(D.TMONEYF,NVL(A.TMONEYF,0)) ELSE NVL(D.TMONEYF,NVL(A.TMONEYF,0)) END AS STD_CUR_AMT
,NVL(D.TWBMONEYF,NVL(A.TWBMONEYF,0)) AS FOR_CY_AMT	,NVL(B.RANGED,0) AS ADJ_PER_MARK	,A.TNUM1F AS QUANTITY	,B.TTIME AS CERT_DATE,NVL(B.ZWHZBZ,0) AS ACCO_STATUS
FROM  XTBILLMX'||I_YEAR||' A
INNER JOIN XTBILL'||I_YEAR||' B ON ( B.BILLID=A.BILLID AND B.VPD_COMPID=A.VPD_COMPID )
INNER JOIN T_ZW_ACCOUNT_RANGE C ON ( B.RANGED=C.RANGED AND C.ACCOUNT_YEAR='||V_YEAR||' AND B.TTIME BETWEEN C.BEGIN_DATE AND C.END_DATE AND C.ACCRANGEID='||V_ACCRANGEID||' )
LEFT JOIN XTBILLFZMX'||I_YEAR||' D ON ( A.BILLID=D.BILLID AND A.BILLID1=D.BILLID1 AND A.VPD_COMPID=D.VPD_COMPID )
WHERE B.BILLID IN ('||I_BILLID|| ') AND B.TTIME <= LAST_DAY(SYSDATE)+1  )
WHERE DIM_GROUP IS NOT NULL '  ;
EXECSQL(V_SQL,'', VC_PROGRAM_NAME||':7 加新');
/*3.通常汇总数据是针对本位币汇总，即人民币。如果发生外币业务，必须有2种汇总数据，一种是直接的外汇总数据，第二种是本位币和外币的汇总数据 */
/*3.1 提取需更新数据到凭证中间表 */
V_SQL := 'INSERT INTO GT_ACC_BILL_DETAIL( BOOK_ID,COMP_ID,SUBJECT_ID,ACC_PD_ID,CERT_DATE,BILL_ID,LOAN_SIGN,REV_MAR,STA_CUR,FOR_CUR,BILL_ID1
,ADJ_PER_MARK,ACCO_STATUS,FOR_CY_AMT,STD_CUR_AMT,QUANTITY )
SELECT X.BOOK_ID,X.COMP_ID,X.SUBJECT_ID,X.ACC_PD_ID,X.CERT_DATE,X.BILL_ID,X.LOAN_SIGN
,X.REV_MAR,X.STA_CUR,X.FOR_CUR,X.BILL_ID1
,X.ADJ_PER_MARK,X.ACCO_STATUS,X.FOR_CY_AMT,X.STD_CUR_AMT,X.QUANTITY
FROM '||V_DETAIL_BACIUP||' X ,GT_BILLFZMX Y
WHERE X.COMP_ID=Y.SEQID AND X.BILL_ID=Y.BILLID1 AND X.ACC_PD_ID=Y.KMID';
EXECSQL(V_SQL,'', VC_PROGRAM_NAME||':8.提取需更新数据');
/*3.2 拆分外币数据到凭证中间表 */
V_SQL := 'INSERT INTO GT_ACC_BILL_DETAIL( BOOK_ID,COMP_ID,SUBJECT_ID,ACC_PD_ID,CERT_DATE,BILL_ID,LOAN_SIGN,REV_MAR,STA_CUR,FOR_CUR,BILL_ID1
,ADJ_PER_MARK,ACCO_STATUS,FOR_CY_AMT,STD_CUR_AMT,QUANTITY )
SELECT X.BOOK_ID,X.COMP_ID,X.SUBJECT_ID,X.ACC_PD_ID,X.CERT_DATE,X.BILL_ID,X.LOAN_SIGN,X.REV_MAR
,-1 AS STA_CUR,X.FOR_CUR,X.BILL_ID1
,X.ADJ_PER_MARK,X.ACCO_STATUS,X.FOR_CY_AMT,X.STD_CUR_AMT,X.QUANTITY
FROM '||V_DETAIL_BACIUP||' X,GT_BILLFZMX Y
WHERE X.COMP_ID=Y.SEQID AND X.BILL_ID=Y.BILLID1 AND X.ACC_PD_ID=Y.KMID AND X.FOR_CUR<>-1';
EXECSQL(V_SQL,'', VC_PROGRAM_NAME||':9.提取外币数据');
/*3.3 提取需更新数据到维度中间表 */
V_SQL := 'INSERT INTO GT_ACC_BILL_DIM( BILL_ID1,BILL_ID2,COMP_ID,LOAN_SIGN,REV_MAR,DIM_GROUP
,STA_CUR,FOR_CUR,STD_CUR_AMT,FOR_CY_AMT,ADJ_PER_MARK,QUANTITY,CERT_DATE,ACCO_STATUS,BOOK_ID,BILL_ID,SUBJECT_ID,ACC_PD_ID )
SELECT    X.BILL_ID1,X.BILL_ID2,X.COMP_ID,X.LOAN_SIGN,X.REV_MAR,X.DIM_GROUP
,X.STA_CUR,X.FOR_CUR,X.STD_CUR_AMT,X.FOR_CY_AMT,X.ADJ_PER_MARK,X.QUANTITY,X.CERT_DATE
,X.ACCO_STATUS,X.BOOK_ID,X.BILL_ID,X.SUBJECT_ID,X.ACC_PD_ID
FROM '||V_DIM_BACIUP||' X,GT_BILLFZMX Y
WHERE X.COMP_ID=Y.SEQID AND X.BILL_ID=Y.BILLID1 AND X.ACC_PD_ID=Y.KMID ' ;
EXECSQL( V_SQL,'', VC_PROGRAM_NAME||':10 提取需更新数据' ) ;
/*3.4 提取外币数据到维度中间表 */
V_SQL := 'INSERT INTO GT_ACC_BILL_DIM( BILL_ID1,BILL_ID2,COMP_ID,LOAN_SIGN,REV_MAR,DIM_GROUP,STA_CUR
,FOR_CUR,STD_CUR_AMT,FOR_CY_AMT,ADJ_PER_MARK,QUANTITY,CERT_DATE,ACCO_STATUS,BOOK_ID,BILL_ID,SUBJECT_ID,ACC_PD_ID )
SELECT    X.BILL_ID1,X.BILL_ID2,X.COMP_ID,X.LOAN_SIGN,X.REV_MAR,X.DIM_GROUP,-1 AS STA_CUR
,X.FOR_CUR,X.STD_CUR_AMT,X.FOR_CY_AMT,X.ADJ_PER_MARK,X.QUANTITY,X.CERT_DATE
,X.ACCO_STATUS,X.BOOK_ID,X.BILL_ID,X.SUBJECT_ID,X.ACC_PD_ID
FROM '||V_DIM_BACIUP||' X,GT_BILLFZMX Y
WHERE X.COMP_ID=Y.SEQID AND X.BILL_ID=Y.BILLID1 AND X.ACC_PD_ID=Y.KMID AND X.FOR_CUR<>-1' ;
EXECSQL( V_SQL,'', VC_PROGRAM_NAME||':11 提取需更新数据' ) ;
/*4 更新维度扩展表实体表，用于生成行维度汇总表 */
/*
V_SQL := 'INSERT INTO ACC_DIM_GROUP(DIM_GROUP,DIM_CLS,DIM_TYPE_ID,DIM_VALUE)
WITH TEMP AS (
SELECT DIM_GROUP,0 DIM_CLS,DIM_VALUE FROM(
SELECT Z.DIM_GROUP,Y.GLDXID,FZDXID1,FZDXID2,FZDXID3,FZDXID4,FZDXID5 ,FLTX1,FLTX2,FLTX3
FROM XTBILLFZMX'||I_YEAR||' X,XTBILLMX'||I_YEAR||' Y ,GT_ACC_BILL_DIM Z
WHERE X.BILLID=Y.BILLID AND X.BILLID1=Y.BILLID1 AND X.BILLID=Z.BILL_ID AND  X.BILLID1=Z.BILL_ID1  AND X.BILLID2=Z.BILL_ID2
GROUP BY Z.DIM_GROUP,Y.GLDXID,X.FZDXID1,X.FZDXID2,X.FZDXID3,X.FZDXID4,X.FZDXID5 ,X.FLTX1,X.FLTX2,X.FLTX3 )
UNPIVOT ( DIM_VALUE FOR DIM_LIST IN (GLDXID,FZDXID1,FZDXID2,FZDXID3,FZDXID4,FZDXID5 ) )
GROUP BY DIM_GROUP,DIM_VALUE
UNION ALL
SELECT DIM_GROUP,1 DIM_CLS,DIM_VALUE FROM(
SELECT Z.DIM_GROUP,Y.GLDXID,FZDXID1,FZDXID2,FZDXID3,FZDXID4,FZDXID5 ,FLTX1,FLTX2,FLTX3
FROM XTBILLFZMX'||I_YEAR||' X,XTBILLMX'||I_YEAR||' Y ,GT_ACC_BILL_DIM Z
WHERE X.BILLID=Y.BILLID AND X.BILLID1=Y.BILLID1 AND X.BILLID=Z.BILL_ID AND  X.BILLID1=Z.BILL_ID1  AND X.BILLID2=Z.BILL_ID2
GROUP BY Z.DIM_GROUP,Y.GLDXID,X.FZDXID1,X.FZDXID2,X.FZDXID3,X.FZDXID4,X.FZDXID5 ,X.FLTX1,X.FLTX2,X.FLTX3 )
UNPIVOT ( DIM_VALUE FOR DIM_LIST IN (FLTX1,FLTX2,FLTX3 ) )
GROUP BY DIM_GROUP,DIM_VALUE
)
SELECT X.DIM_GROUP,X.DIM_CLS
,NVL(CASE WHEN X.DIM_CLS=0 THEN B.DXLXID ELSE SUBSTR(C.ITEMCODE,1,4) END,-1) AS DIM_TYPE
,X.DIM_VALUE
FROM TEMP X
LEFT JOIN ACC_DIM_GROUP A   ON X.DIM_GROUP=A.DIM_GROUP
LEFT JOIN XTGLDXIDMC B      ON X.DIM_VALUE=B.DXID
LEFT JOIN T_XTITEMSYW_EXT C ON X.DIM_VALUE = C.IID    AND C.COMPID='''||V_COMPID_TOP||'''    AND C.TYEAR='||V_YEAR||' AND LEAF=1
WHERE A.DIM_GROUP IS NULL  ' ;
EXECSQL(V_SQL,'', VC_PROGRAM_NAME||':12.更新维度扩展表') ;
*/
--4.1.把需要更新的dim_group加入到临时表
INSERT INTO GT_GLDXIDMC(MC)
SELECT A.DIM_GROUP
FROM GT_ACC_BILL_DIM A LEFT JOIN ACC_DIM_GROUP B ON A.DIM_GROUP=B.DIM_GROUP WHERE B.DIM_GROUP IS NULL GROUP BY A.DIM_GROUP;
--4.2.判断是否需要更新ACC_DIM_GROUP
SELECT COUNT(1) INTO V_IS_EXISTS FROM GT_GLDXIDMC WHERE ROWNUM < 2;
IF V_IS_EXISTS>0 THEN
--DIM_GROUP 行转列
DELETE FROM GT_CHECK_XTGLDX;
INSERT INTO GT_CHECK_XTGLDX(DXMC, KEY_VALUE, DXID)
SELECT DIM_GROUP, DIM_CLS, DIM_VALUE
FROM (SELECT DIM_GROUP
,CASE WHEN DXP1>0 THEN SUBSTR(DX_LIST,1,DXP1-1) END AS DX1
,CASE WHEN DXP2>0 THEN SUBSTR(DX_LIST,DXP1+1, DXP2-DXP1-1) END AS DX2
,CASE WHEN DXP3>0 THEN SUBSTR(DX_LIST,DXP2+1, DXP3-DXP2-1) END AS DX3
,CASE WHEN DXP4>0 THEN SUBSTR(DX_LIST,DXP3+1, DXP4-DXP3-1) END AS DX4
,CASE WHEN DXP5>0 THEN SUBSTR(DX_LIST,DXP4+1, DXP5-DXP4-1) END AS DX5
,CASE WHEN DXP6>0 THEN SUBSTR(DX_LIST,DXP5+1, DXP6-DXP5-1) END AS DX6
,CASE WHEN FLP1>0 THEN SUBSTR(FL_LIST,1,FLP1-1) END AS FL1
,CASE WHEN FLP2>0 THEN SUBSTR(FL_LIST,FLP1+1, FLP2-FLP1-1) END AS FL2
,CASE WHEN FLP3>0 THEN SUBSTR(FL_LIST,FLP2+1, FLP3-FLP2-1) END AS FL3
FROM (SELECT DIM_GROUP, DX_LIST, FL_LIST
,INSTR(DX_LIST,',') AS DXP1
,INSTR(DX_LIST,',',1,2) AS DXP2
,INSTR(DX_LIST,',',1,3) AS DXP3
,INSTR(DX_LIST,',',1,4) AS DXP4
,INSTR(DX_LIST,',',1,5) AS DXP5
,INSTR(DX_LIST,',',1,6) AS DXP6
,INSTR(FL_LIST,',') AS FLP1
,INSTR(FL_LIST,',',1,2) AS FLP2
,INSTR(FL_LIST,',',1,3) AS FLP3
FROM (SELECT MC AS DIM_GROUP
,CASE WHEN SUBSTR(MC,1,2)='0:'
THEN CASE WHEN INSTR(MC,';')>0 THEN SUBSTR(MC,3, INSTR(MC,';')-3) ELSE SUBSTR(MC,3) END ||','
END AS DX_LIST
,CASE WHEN SUBSTR(MC,1,2)='1:'
THEN SUBSTR(MC,3) ||',' WHEN INSTR(MC,';')>0 THEN SUBSTR(MC,INSTR(MC,';')+3) ||','
END AS FL_LIST
FROM GT_GLDXIDMC C
) D
) E
) F
UNPIVOT (DIM_VALUE FOR DIM_CLS IN (DX1 AS 0 ,DX2 AS 0 ,DX3 AS 0 ,DX4 AS 0 ,DX5 AS 0 ,DX6 AS 0 ,FL1 AS 1 ,FL2 AS 1 ,FL3 AS 1));
--4.3 新增DIM_GROUP
V_SQL := '	INSERT INTO ACC_DIM_GROUP(DIM_GROUP, DIM_CLS, DIM_TYPE_ID, DIM_VALUE,CRE_TIME)
SELECT
G.DXMC, G.KEY_VALUE, NVL(CASE WHEN G.KEY_VALUE=0 THEN H.DXLXID ELSE I.ITEMCODE END,-1), G.DXID,SYSDATE
FROM GT_CHECK_XTGLDX G
LEFT JOIN XTGLDXIDMC H
ON G.KEY_VALUE=0 AND G.DXID=H.DXID
LEFT JOIN (SELECT DISTINCT IID, SUBSTR(ITEMCODE,1,4) AS ITEMCODE FROM T_XTITEMSYW_EXT WHERE COMPID='''||V_COMPID_TOP||'''  AND TYEAR='||V_YEAR||' AND LEAF=1) I
ON G.KEY_VALUE=1 AND G.DXID=I.IID ';
EXECSQL(V_SQL,'', VC_PROGRAM_NAME||':12.更新维度扩展表') ;
END IF;
/*5.初始化期初额，取上年最后一个账期的期末值 ,如果上年科目期末结零，那么期初值为0 */
/*5.1 首次记账时，初始化上年最后一个账期期末值作为本年期初值 ,插入到科目汇总中间表 */
V_SQL := 'INSERT INTO GT_GENERAL_SES1(F_NUM1,F_NUM2,F_NUM3,F_NUM4,F_NUM5,COMPID)
SELECT X.SUBJECT_ID , X.STA_CUR , X.FOR_CUR , X.ACCO_STATUS , X.BOOK_ID , X.COMP_ID
FROM GT_ACC_BILL_DETAIL X ,ACC_BAL_SUM'||I_YEAR||' Y
WHERE X.SUBJECT_ID=Y.SUBJECT_ID(+) AND X.STA_CUR=Y.STA_CUR(+)
AND X.FOR_CUR=Y.FOR_CUR(+) AND X.ACCO_STATUS=Y.ACCO_STATUS(+)
AND X.BOOK_ID=Y.BOOK_ID(+) AND X.COMP_ID=Y.COMP_ID(+) AND Y.COMP_ID IS NULL
GROUP BY X.SUBJECT_ID , X.STA_CUR , X.FOR_CUR , X.ACCO_STATUS , X.BOOK_ID , X.COMP_ID' ;
EXECUTE IMMEDIATE V_SQL;
V_SQL := 'INSERT INTO GT_ACC_BAL_SUM
(
BOOK_ID		'|| -- 	账簿ID
'	,COMP_ID		'|| -- 	财务组织ID
'	,ACC_PD_ID		'|| --	会计期间
'	,ITEM_CODE		'|| --	会计科目ITEMCODE
'  	,SUBJECT_ID		'|| --	会计科目
'  	,ACCO_STATUS	'|| --	总状态标志
'  	,STA_CUR		'|| --	本位币币种
'  	,FOR_CUR		'|| --	外币币种
'    ,OB_DE_AMOUNT	'|| --	期初借方累计
'    ,OB_CR_AMOUNT	'|| --	期初贷方累计
'    ,CB_DE_AMOUNT	'|| --	期末借方累计
'    ,CB_CR_AMOUNT	'|| --	期末贷方累计
'    ,AN_DE_AMOUNT	'|| --	年借方累计
'    ,AN_CR_AMOUNT	'|| --	年贷方累计
'    ,CR_AMT_TOT	'|| --	贷方金额合计
'    ,DR_AMT_TOT	'|| --	借方金额合计
'    ,SE_DE_AMOUNT	'|| --	季借方累计
'    ,SE_CR_AMOUNT	'|| --	季贷方累计
'    ,IN_DE_OP_BAL 	'|| --	只取期初借方余额
'    ,IN_CR_OP_BAL 	'|| --	只取期初贷方余额
'    ,IN_DE_CL_BAL 	'|| --	只取期末借方余额
'    ,IN_CR_CL_BAL 	'|| --	只取期末贷方余额
'      )
SELECT
X.BOOK_ID		,X.COMP_ID		,T.ACCOUNT_ID	,X.ITEMCODE
,X.SUBJECT_ID	,X.ACCO_STATUS	,X.STA_CUR
,X.FOR_CUR
,CASE WHEN Q.GLDXID IS NULL THEN NVL(Y.CB_DE_AMOUNT,0) ELSE 0 END AS QC_J
,CASE WHEN Q.GLDXID IS NULL THEN NVL(Y.CB_CR_AMOUNT,0)	ELSE 0 END AS QC_D
,CASE WHEN Q.GLDXID IS NULL THEN NVL(Y.CB_DE_AMOUNT,0) ELSE 0 END AS QM_J
,CASE WHEN Q.GLDXID IS NULL THEN NVL(Y.CB_CR_AMOUNT,0) ELSE 0 END AS QM_D
,0,0,0,0,0,0
,NVL(Y.OB_DE_AMOUNT,0)     --只取期初借方余额
,NVL(Y.OB_CR_AMOUNT,0)     --只取期初贷方余额
,NVL(Y.CB_DE_AMOUNT,0)     --只取期末借方余额
,NVL(Y.CB_CR_AMOUNT,0)     --只取期末贷方余额
FROM ( SELECT A.BOOK_ID,A.COMP_ID,A.SUBJECT_ID,A.ACCO_STATUS,A.STA_CUR,A.FOR_CUR ,B.ITEMCODE
FROM GT_ACC_BILL_DETAIL A,T_ZWITEMSYW_EXT B ,GT_GENERAL_SES1 C
WHERE A.SUBJECT_ID=B.IID    AND B.TYEAR='||V_YEAR||'   AND B.LEAF=1 AND B.COMPID='''||V_MANAGECOMPID||'''
AND A.SUBJECT_ID=C.F_NUM1  AND A.STA_CUR=C.F_NUM2 	AND A.FOR_CUR=C.F_NUM3
AND A.ACCO_STATUS=C.F_NUM4  AND A.BOOK_ID=C.F_NUM5  AND A.COMP_ID=C.COMPID
GROUP BY  A.BOOK_ID,A.COMP_ID,A.SUBJECT_ID,A.ACCO_STATUS,A.STA_CUR,A.FOR_CUR ,B.ITEMCODE ) X
INNER JOIN T_ZW_ACCOUNT_RANGE T ON ( T.ACCRANGEID='||TO_CHAR(V_ACCRANGEID)||'  AND T.ACCOUNT_YEAR='||I_YEAR||' AND T.ACCOUNT_ID <= '||V_NOW_ACCID||' )
LEFT JOIN ACC_BAL_SUM'||TO_CHAR(V_YEAR-1)||' Y
ON ( X.BOOK_ID=Y.BOOK_ID      AND X.COMP_ID=Y.COMP_ID      AND X.SUBJECT_ID=Y.SUBJECT_ID
AND Y.ACC_PD_ID=TO_NUMBER('||TO_CHAR(V_YEAR-1)||'1202 )
AND X.ACCO_STATUS=Y.ACCO_STATUS     AND X.STA_CUR=Y.STA_CUR     AND X.FOR_CUR=Y.FOR_CUR  )
LEFT JOIN HZBB_ZHCX_GLDX Q ON ( X.SUBJECT_ID=Q.GLDXID AND X.COMP_ID=Q.DWDH )      ' ;
EXECSQL(V_SQL,'', VC_PROGRAM_NAME||':13初始化期初额1');
V_SQL := 'INSERT INTO GT_ACC_DIM_SUM
( 	 BOOK_ID		,COMP_ID		,ACC_PD_ID		,ITEM_CODE
,SUBJECT_ID		,ACCO_STATUS	,STA_CUR
,FOR_CUR		,DIM_GROUP
,OB_DE_AMOUNT	,OB_CR_AMOUNT	,CB_DE_AMOUNT	,CB_CR_AMOUNT
,AN_DE_AMOUNT	,AN_CR_AMOUNT	,CR_AMT_TOT
,DR_AMT_TOT		,SE_DE_AMOUNT	,SE_CR_AMOUNT  )
SELECT
X.BOOK_ID		,X.COMP_ID		,T.ACCOUNT_ID	,X.ITEMCODE
,X.SUBJECT_ID	,X.ACCO_STATUS	,X.STA_CUR		,X.FOR_CUR
,X.DIM_GROUP
,CASE WHEN Q.GLDXID IS NULL THEN NVL(Y.CB_DE_AMOUNT,0)	ELSE 0 END AS QC_J
,CASE WHEN Q.GLDXID IS NULL THEN NVL(Y.CB_CR_AMOUNT,0) ELSE 0 END AS QC_D
,CASE WHEN Q.GLDXID IS NULL THEN NVL(Y.CB_DE_AMOUNT,0)	ELSE 0 END AS QM_J
,CASE WHEN Q.GLDXID IS NULL THEN NVL(Y.CB_CR_AMOUNT,0)	ELSE 0 END AS QM_D
,0,0,0,0,0,0
FROM ( SELECT A.BOOK_ID,A.COMP_ID,A.SUBJECT_ID,A.ACCO_STATUS,A.STA_CUR,A.FOR_CUR,A.DIM_GROUP ,B.ITEMCODE
FROM GT_ACC_BILL_DIM A ,T_ZWITEMSYW_EXT B ,GT_GENERAL_SES1 C
WHERE A.SUBJECT_ID=B.IID    AND B.TYEAR='||V_YEAR||'   AND B.LEAF=1 AND B.COMPID='''||V_MANAGECOMPID||'''
AND A.SUBJECT_ID=C.F_NUM1  AND A.STA_CUR=C.F_NUM2 	AND A.FOR_CUR=C.F_NUM3
AND A.ACCO_STATUS=C.F_NUM4  AND A.BOOK_ID=C.F_NUM5  AND A.COMP_ID=C.COMPID
GROUP BY  A.BOOK_ID,A.COMP_ID,A.SUBJECT_ID,A.ACCO_STATUS,A.STA_CUR,A.FOR_CUR,A.DIM_GROUP ,B.ITEMCODE ) X
INNER JOIN T_ZW_ACCOUNT_RANGE T ON ( T.ACCRANGEID='||TO_CHAR(V_ACCRANGEID)||'   AND T.ACCOUNT_YEAR='||I_YEAR||' AND T.ACCOUNT_ID <= '||V_NOW_ACCID||')
LEFT JOIN ACC_BAL_DIM_SUM'||TO_CHAR(V_YEAR-1)||' Y
ON ( X.BOOK_ID=Y.BOOK_ID AND X.COMP_ID=Y.COMP_ID AND X.SUBJECT_ID=Y.SUBJECT_ID AND X.ACCO_STATUS=Y.ACCO_STATUS
AND X.STA_CUR=Y.STA_CUR AND X.FOR_CUR=Y.FOR_CUR AND X.DIM_GROUP=Y.DIM_GROUP
AND Y.ACC_PD_ID=TO_NUMBER('||TO_CHAR(V_YEAR-1)||'1202 )  )
LEFT JOIN HZBB_ZHCX_GLDX Q ON ( X.SUBJECT_ID=Q.GLDXID AND X.COMP_ID=Q.DWDH )  ' ;
EXECSQL(V_SQL,'', VC_PROGRAM_NAME||':14 初始化期初额2') ;
-- END IF ;
/*6 将数据按帐期、单位、币种、科目,汇总到科目汇总临时表，科目必须存在T_ZWITEMSYW_EXT */
/*6.1 生成发生账期以及以后账期数据*/
/* 未记账数据只存在本期，不累计到后面月份*/
V_SQL := 'INSERT INTO GT_ACC_BAL_SUM
(
BOOK_ID	    ,COMP_ID		,ACC_PD_ID		,ITEM_CODE
,SUBJECT_ID	,ACCO_STATUS	,STA_CUR		,FOR_CUR
,DR_AMT_TOT		,CR_AMT_TOT 	,OB_DE_AMOUNT	,OB_CR_AMOUNT
,AN_DE_AMOUNT 	,AN_CR_AMOUNT 	,CB_DE_AMOUNT	,CB_CR_AMOUNT
,SE_DE_AMOUNT	,SE_CR_AMOUNT
,IN_DE_OP_BAL 	,IN_CR_OP_BAL 	,IN_DE_CL_BAL 	,IN_CR_CL_BAL
)
WITH TEMP AS (
SELECT A.BOOK_ID,A.COMP_ID,A.ACC_PD_ID,A.SUBJECT_ID,A.ACCO_STATUS,A.STA_CUR,A.FOR_CUR,B.ITEMCODE AS ITEM_CODE
,SUM(CASE WHEN A.STA_CUR<>-1  AND A.LOAN_SIGN=1 AND REV_MAR=1 THEN A.STD_CUR_AMT*-1
WHEN A.STA_CUR<>-1  AND A.LOAN_SIGN=1 AND REV_MAR=0 THEN A.STD_CUR_AMT
WHEN A.STA_CUR=-1   AND A.LOAN_SIGN=1 AND REV_MAR=1 THEN A.FOR_CY_AMT*-1
WHEN A.STA_CUR=-1   AND A.LOAN_SIGN=1 AND REV_MAR=0 THEN A.FOR_CY_AMT
ELSE 0 END ) AS SUM_D
,SUM(CASE WHEN A.STA_CUR<>-1  AND A.LOAN_SIGN=0 AND REV_MAR=1 THEN STD_CUR_AMT*-1
WHEN A.STA_CUR<>-1  AND A.LOAN_SIGN=0 AND REV_MAR=0 THEN STD_CUR_AMT
WHEN A.STA_CUR=-1   AND A.LOAN_SIGN=0 AND REV_MAR=1 THEN FOR_CY_AMT*-1
WHEN A.STA_CUR=-1   AND A.LOAN_SIGN=0 AND REV_MAR=0 THEN FOR_CY_AMT
ELSE 0 END ) AS SUM_J
FROM GT_ACC_BILL_DETAIL A ,T_ZWITEMSYW_EXT B
WHERE A.SUBJECT_ID=B.IID    AND B.TYEAR='||V_YEAR||'   AND B.LEAF=1 AND B.COMPID='''||V_MANAGECOMPID||'''
GROUP BY A.BOOK_ID,A.COMP_ID,ACC_PD_ID,SUBJECT_ID,STA_CUR,FOR_CUR,ACCO_STATUS,B.ITEMCODE
)
SELECT X.BOOK_ID		,X.COMP_ID		,Z.ACCOUNT_ID	,X.ITEM_CODE
,X.SUBJECT_ID	,X.ACCO_STATUS  ,X.STA_CUR		,X.FOR_CUR
,SUM(CASE WHEN Z.ACCOUNT_ID = X.ACC_PD_ID THEN X.SUM_J ELSE 0 END ) AS SUM_J        -- 借方本期发生数，只更新本期数据
,SUM(CASE WHEN Z.ACCOUNT_ID = X.ACC_PD_ID THEN X.SUM_D ELSE 0 END ) AS SUM_D        -- 贷方本期发生数，只更新本期数据
,SUM(CASE WHEN X.ACCO_STATUS = 0 AND Z.ACCOUNT_ID <> X.ACC_PD_ID THEN 0
ELSE CASE WHEN Z.ACCOUNT_ID = X.ACC_PD_ID THEN 0 ELSE X.SUM_J END END ) AS SUM_J_QC     -- 借方期初数,本期不更新，后面月份都更新
,SUM(CASE WHEN X.ACCO_STATUS = 0 AND Z.ACCOUNT_ID <> X.ACC_PD_ID THEN 0
ELSE CASE WHEN Z.ACCOUNT_ID = X.ACC_PD_ID THEN 0 ELSE X.SUM_D END END ) AS SUM_D_QC     -- 贷方期初数,本期不更新，后面月份都更新
,SUM(CASE WHEN X.ACCO_STATUS = 0 AND Z.ACCOUNT_ID <> X.ACC_PD_ID THEN 0
ELSE CASE WHEN S.RANGED=1 AND S.ACCOUNT_MONTH=0 THEN 0
ELSE X.SUM_J END END ) AS SUM_J_BNLJ  -- 本年累计数,不含年初调整帐期，当期及后面月份都更新
,SUM(CASE WHEN X.ACCO_STATUS = 0 AND Z.ACCOUNT_ID <> X.ACC_PD_ID THEN 0
ELSE CASE WHEN S.RANGED=1 AND S.ACCOUNT_MONTH=0 THEN 0
ELSE X.SUM_D END END ) AS SUM_D_BNLJ  -- 本年累计数,不含年初调整帐期，当期及后面月份都更新
,SUM(CASE WHEN X.ACCO_STATUS = 0 AND Z.ACCOUNT_ID <> X.ACC_PD_ID THEN 0 ELSE X.SUM_J END ) AS SUM_J_QM    -- 借方期末数，当期及后面月份都更新
,SUM(CASE WHEN X.ACCO_STATUS = 0 AND Z.ACCOUNT_ID <> X.ACC_PD_ID THEN 0 ELSE X.SUM_D END ) AS SUM_D_QM    -- 贷方期末数，当期及后面月份都更新
,SUM(CASE WHEN X.ACCO_STATUS = 0 AND Z.ACCOUNT_ID <> X.ACC_PD_ID THEN 0 ELSE
CASE S.RANGED + Z.RANGED
WHEN 0 THEN CASE WHEN Z.ACCOUNT_SEASON=S.ACCOUNT_SEASON THEN X.SUM_J ELSE 0 END
ELSE 0
END END)   AS SUM_J_JD    	--季度借方数
,SUM(CASE WHEN X.ACCO_STATUS = 0 AND Z.ACCOUNT_ID <> X.ACC_PD_ID THEN 0 ELSE
CASE S.RANGED + Z.RANGED
WHEN 0 THEN CASE WHEN Z.ACCOUNT_SEASON=S.ACCOUNT_SEASON THEN X.SUM_D ELSE 0 END
ELSE 0
END END )   AS SUM_D_JD	 	--季度贷方数
,0,0,0,0
FROM TEMP X
INNER JOIN T_ZW_ACCOUNT_RANGE Z ON ( Z.ACCRANGEID='||TO_CHAR(V_ACCRANGEID)||'   AND Z.ACCOUNT_ID >= X.ACC_PD_ID
AND Z.ACCOUNT_YEAR='||I_YEAR||' AND Z.ACCOUNT_ID <= '||V_NOW_ACCID||' )
INNER JOIN T_ZW_ACCOUNT_RANGE S ON ( S.ACCRANGEID='||TO_CHAR(V_ACCRANGEID)||'   AND S.ACCOUNT_ID =  X.ACC_PD_ID   AND S.ACCOUNT_YEAR='||I_YEAR||' )
GROUP BY	X.BOOK_ID		,X.COMP_ID		,Z.ACCOUNT_ID	,X.ITEM_CODE
,X.SUBJECT_ID	,X.ACCO_STATUS  ,X.STA_CUR		,X.FOR_CUR	  ' ;
EXECSQL( V_SQL,'', VC_PROGRAM_NAME||':15 汇总科目数' ) ;
/*6.2 将数据按帐期、单位、币种、科目、维度组合汇总到维度汇总临时表*/
/* 未记账数据只存在本期，不累计到后面月份*/
V_SQL := 'INSERT INTO GT_ACC_DIM_SUM
(
BOOK_ID
,COMP_ID
,ACC_PD_ID
,SUBJECT_ID
,ITEM_CODE
,STA_CUR
,FOR_CUR
,ACCO_STATUS
,DIM_GROUP
,DR_AMT_TOT
,CR_AMT_TOT
,OB_DE_AMOUNT
,OB_CR_AMOUNT
,AN_DE_AMOUNT
,AN_CR_AMOUNT
,CB_DE_AMOUNT
,CB_CR_AMOUNT
,SE_DE_AMOUNT
,SE_CR_AMOUNT  )
WITH TEMP AS (
SELECT A.BOOK_ID,A.COMP_ID,A.ACC_PD_ID,A.SUBJECT_ID,A.STA_CUR,A.FOR_CUR,A.ACCO_STATUS,A.DIM_GROUP,B.ITEMCODE
,SUM(CASE WHEN A.STA_CUR<>-1  AND A.LOAN_SIGN=1 AND REV_MAR=1 THEN A.STD_CUR_AMT*-1
WHEN A.STA_CUR<>-1  AND A.LOAN_SIGN=1 AND REV_MAR=0 THEN A.STD_CUR_AMT
WHEN A.STA_CUR=-1   AND A.LOAN_SIGN=1 AND REV_MAR=1 THEN A.FOR_CY_AMT*-1
WHEN A.STA_CUR=-1   AND A.LOAN_SIGN=1 AND REV_MAR=0 THEN A.FOR_CY_AMT
ELSE 0 END ) AS SUM_D
,SUM(CASE WHEN A.STA_CUR<>-1  AND A.LOAN_SIGN=0 AND REV_MAR=1 THEN STD_CUR_AMT*-1
WHEN A.STA_CUR<>-1  AND A.LOAN_SIGN=0 AND REV_MAR=0 THEN STD_CUR_AMT
WHEN A.STA_CUR=-1   AND A.LOAN_SIGN=0 AND REV_MAR=1 THEN FOR_CY_AMT*-1
WHEN A.STA_CUR=-1   AND A.LOAN_SIGN=0 AND REV_MAR=0 THEN FOR_CY_AMT
ELSE 0 END ) AS SUM_J
FROM GT_ACC_BILL_DIM A ,T_ZWITEMSYW_EXT B
WHERE A.SUBJECT_ID=B.IID AND B.TYEAR='||V_YEAR||' AND B.LEAF=1 AND B.COMPID='''||V_MANAGECOMPID||'''
GROUP BY A.BOOK_ID,A.COMP_ID,A.ACC_PD_ID,A.SUBJECT_ID,A.STA_CUR,A.FOR_CUR,A.ACCO_STATUS,A.DIM_GROUP,B.ITEMCODE
)
SELECT X.BOOK_ID		,X.COMP_ID		,Z.ACCOUNT_ID	 ,X.SUBJECT_ID		,X.ITEMCODE
,X.STA_CUR		, X.FOR_CUR		,X.ACCO_STATUS	 ,X.DIM_GROUP
,SUM(CASE WHEN Z.ACCOUNT_ID = X.ACC_PD_ID THEN X.SUM_J ELSE 0 END ) AS SUM_J        -- 借方本期发生数，只更新本期数据
,SUM(CASE WHEN Z.ACCOUNT_ID = X.ACC_PD_ID THEN X.SUM_D ELSE 0 END ) AS SUM_D        -- 贷方本期发生数，只更新本期数据
,SUM(CASE WHEN X.ACCO_STATUS = 0 AND Z.ACCOUNT_ID <> X.ACC_PD_ID THEN 0
ELSE CASE WHEN Z.ACCOUNT_ID = X.ACC_PD_ID THEN 0 ELSE X.SUM_J END END ) AS SUM_J_QC     -- 借方期初数,本期不更新，后面月份都更新
,SUM(CASE WHEN X.ACCO_STATUS = 0 AND Z.ACCOUNT_ID <> X.ACC_PD_ID THEN 0
ELSE CASE WHEN Z.ACCOUNT_ID = X.ACC_PD_ID THEN 0 ELSE X.SUM_D END END ) AS SUM_D_QC     -- 贷方期初数,本期不更新，后面月份都更新
,SUM(CASE WHEN X.ACCO_STATUS = 0 AND Z.ACCOUNT_ID <> X.ACC_PD_ID THEN 0
ELSE CASE WHEN S.RANGED=1 AND S.ACCOUNT_MONTH=0 THEN 0
ELSE X.SUM_J END END ) AS SUM_J_BNLJ  -- 本年累计数,不含年初调整帐期，当期及后面月份都更新
,SUM(CASE WHEN X.ACCO_STATUS = 0 AND Z.ACCOUNT_ID <> X.ACC_PD_ID THEN 0
ELSE CASE WHEN S.RANGED=1 AND S.ACCOUNT_MONTH=0 THEN 0
ELSE X.SUM_D END END ) AS SUM_D_BNLJ  -- 本年累计数,不含年初调整帐期，当期及后面月份都更新
,SUM(CASE WHEN X.ACCO_STATUS = 0 AND Z.ACCOUNT_ID <> X.ACC_PD_ID THEN 0 ELSE X.SUM_J END ) AS SUM_J_QM    -- 借方期末数，当期及后面月份都更新
,SUM(CASE WHEN X.ACCO_STATUS = 0 AND Z.ACCOUNT_ID <> X.ACC_PD_ID THEN 0 ELSE X.SUM_D END ) AS SUM_D_QM    -- 贷方期末数，当期及后面月份都更新
,SUM(CASE WHEN X.ACCO_STATUS = 0 AND Z.ACCOUNT_ID <> X.ACC_PD_ID THEN 0 ELSE
CASE S.RANGED + Z.RANGED
WHEN 0 THEN CASE WHEN Z.ACCOUNT_SEASON=S.ACCOUNT_SEASON THEN X.SUM_J ELSE 0 END
ELSE 0
END END)   AS SUM_J_JD		--季度借方数
,SUM(CASE WHEN X.ACCO_STATUS = 0 AND Z.ACCOUNT_ID <> X.ACC_PD_ID THEN 0 ELSE
CASE S.RANGED + Z.RANGED
WHEN 0 THEN CASE WHEN Z.ACCOUNT_SEASON=S.ACCOUNT_SEASON THEN X.SUM_D ELSE 0 END
ELSE 0
END END )   AS SUM_D_JD		--季度贷方数
FROM TEMP X
INNER JOIN T_ZW_ACCOUNT_RANGE Z ON ( Z.ACCRANGEID='||TO_CHAR(V_ACCRANGEID)||'   AND Z.ACCOUNT_ID >= X.ACC_PD_ID
AND Z.ACCOUNT_YEAR='||I_YEAR||' AND Z.ACCOUNT_ID <= '||V_NOW_ACCID||' )
INNER JOIN T_ZW_ACCOUNT_RANGE S ON ( S.ACCRANGEID='||TO_CHAR(V_ACCRANGEID)||'   AND S.ACCOUNT_ID =  X.ACC_PD_ID   AND S.ACCOUNT_YEAR='||I_YEAR||' )
GROUP BY X.BOOK_ID,X.COMP_ID,Z.ACCOUNT_ID,X.SUBJECT_ID,X.ITEMCODE,X.STA_CUR, X.FOR_CUR,X.ACCO_STATUS,X.DIM_GROUP ' ;
EXECSQL(V_SQL,'', VC_PROGRAM_NAME||':16 将数据汇总维度') ;
/*7.构造底层组织与合并层组织关系表 */
INSERT INTO GT_HZ_COMPANYINFO_SES( COMPID,PCOMPID,PMANAGECOMPID )
SELECT ROOT_COMPID,COMPID,OWNER_MAN_COMP_ID
FROM (
SELECT COMPID,OWNER_MAN_COMP_ID,CONNECT_BY_ROOT(COMPID) AS ROOT_COMPID FROM
( SELECT COMPID,PCOMPID,OWNER_MAN_COMP_ID FROM COMPANYINFO WHERE VPD_YEAR=V_YEAR AND DCCODE=V_DCCODE
AND ACCBOOKID=0 )
CONNECT BY COMPID = PRIOR PCOMPID
START WITH COMPID IN ( SELECT COMP_ID FROM GT_ACC_BAL_SUM GROUP BY COMP_ID )
)
WHERE  COMPID<>'XXXX' ;  --XXXX为特殊标记，表示合并外单位: 如单位1为底层单位，其上级是单位2，单位2的上级单位为XXXX，此时，单位2的数是要汇的，但XXXX不要汇
/*8.汇总临时表与帐期表作迪卡尔关联，MERGE到汇总表中，把数值加到本月发生额，以及加到后面每个月的年借贷方累计,期末借贷方累计,期初借贷方累计中,当月发生额为0 */
/*底层单位科目汇总表 */
V_SQL:='MERGE  INTO ACC_BAL_SUM'||I_YEAR||' A
USING ( SELECT X.BOOK_ID,X.COMP_ID,X.ACC_PD_ID,X.SUBJECT_ID,X.STA_CUR,X.FOR_CUR,X.ACCO_STATUS,X.ITEM_CODE
,SUM(OB_DE_AMOUNT)  AS OB_DE_AMOUNT
,SUM(OB_CR_AMOUNT)  AS OB_CR_AMOUNT
,SUM(AN_DE_AMOUNT)  AS AN_DE_AMOUNT
,SUM(AN_CR_AMOUNT)  AS AN_CR_AMOUNT
,SUM(CB_DE_AMOUNT)  AS CB_DE_AMOUNT
,SUM(CB_CR_AMOUNT)  AS CB_CR_AMOUNT
,SUM(CR_AMT_TOT  )  AS CR_AMT_TOT
,SUM(DR_AMT_TOT  )  AS DR_AMT_TOT
,SUM(SE_CR_AMOUNT)  AS SE_CR_AMOUNT
,SUM(SE_DE_AMOUNT)  AS SE_DE_AMOUNT
FROM GT_ACC_BAL_SUM X
GROUP BY X.BOOK_ID,X.COMP_ID,X.ACC_PD_ID,X.SUBJECT_ID,X.STA_CUR,X.FOR_CUR,X.ACCO_STATUS,X.ITEM_CODE ) B
ON (  A.BOOK_ID=B.BOOK_ID                   AND A.COMP_ID=B.COMP_ID
AND A.ACC_PD_ID=B.ACC_PD_ID          AND A.SUBJECT_ID=B.SUBJECT_ID             AND A.STA_CUR=B.STA_CUR
AND A.FOR_CUR=B.FOR_CUR               AND A.ACCO_STATUS=B.ACCO_STATUS )
WHEN MATCHED THEN
UPDATE SET  A.OB_DE_AMOUNT  =A.OB_DE_AMOUNT                 +B.OB_DE_AMOUNT
,A.OB_CR_AMOUNT  =A.OB_CR_AMOUNT                 +B.OB_CR_AMOUNT
,A.AN_DE_AMOUNT  =A.AN_DE_AMOUNT                 +B.AN_DE_AMOUNT
,A.AN_CR_AMOUNT  =A.AN_CR_AMOUNT                 +B.AN_CR_AMOUNT
,A.CB_DE_AMOUNT  =A.CB_DE_AMOUNT                 +B.CB_DE_AMOUNT
,A.CB_CR_AMOUNT  =A.CB_CR_AMOUNT                 +B.CB_CR_AMOUNT
,A.DR_AMT_TOT    =A.DR_AMT_TOT                   +B.DR_AMT_TOT
,A.CR_AMT_TOT    =A.CR_AMT_TOT                   +B.CR_AMT_TOT
,A.SE_DE_AMOUNT  =A.SE_DE_AMOUNT                 +B.SE_DE_AMOUNT
,A.SE_CR_AMOUNT  =A.SE_CR_AMOUNT                 +B.SE_CR_AMOUNT
WHEN NOT MATCHED THEN
INSERT (BOOK_ID,COMP_ID,ACC_PD_ID,SUBJECT_ID,STA_CUR,FOR_CUR,ACCO_STATUS
,OB_DE_AMOUNT,OB_CR_AMOUNT,AN_DE_AMOUNT,AN_CR_AMOUNT
,CB_DE_AMOUNT,CB_CR_AMOUNT,CR_AMT_TOT,DR_AMT_TOT
,SE_CR_AMOUNT,SE_DE_AMOUNT,ITEM_CODE)
VALUES (B.BOOK_ID,B.COMP_ID,B.ACC_PD_ID,B.SUBJECT_ID,B.STA_CUR,B.FOR_CUR,B.ACCO_STATUS
,B.OB_DE_AMOUNT,B.OB_CR_AMOUNT,B.AN_DE_AMOUNT,B.AN_CR_AMOUNT
,B.CB_DE_AMOUNT,B.CB_CR_AMOUNT,B.CR_AMT_TOT,B.DR_AMT_TOT
,B.SE_CR_AMOUNT,B.SE_DE_AMOUNT,B.ITEM_CODE) ';
EXECSQL(V_SQL,'', VC_PROGRAM_NAME||':17.汇总底层单位科目');
/*底层单位维度汇总表*/
V_SQL:='MERGE  INTO ACC_BAL_DIM_SUM'||I_YEAR||' A
USING ( SELECT BOOK_ID,COMP_ID,ACC_PD_ID,SUBJECT_ID,STA_CUR,FOR_CUR,ACCO_STATUS,DIM_GROUP,ITEM_CODE
,SUM(OB_DE_AMOUNT)  AS OB_DE_AMOUNT
,SUM(OB_CR_AMOUNT)  AS OB_CR_AMOUNT
,SUM(AN_DE_AMOUNT)  AS AN_DE_AMOUNT
,SUM(AN_CR_AMOUNT)  AS AN_CR_AMOUNT
,SUM(CB_DE_AMOUNT)  AS CB_DE_AMOUNT
,SUM(CB_CR_AMOUNT)  AS CB_CR_AMOUNT
,SUM(CR_AMT_TOT  )  AS CR_AMT_TOT
,SUM(DR_AMT_TOT  )  AS DR_AMT_TOT
,SUM(SE_CR_AMOUNT)  AS SE_CR_AMOUNT
,SUM(SE_DE_AMOUNT)  AS SE_DE_AMOUNT
FROM GT_ACC_DIM_SUM
GROUP BY BOOK_ID,COMP_ID,ACC_PD_ID,SUBJECT_ID,STA_CUR,FOR_CUR,ACCO_STATUS,DIM_GROUP,ITEM_CODE )  B
ON ( A.BOOK_ID=B.BOOK_ID              AND A.COMP_ID=B.COMP_ID
AND A.ACC_PD_ID=B.ACC_PD_ID     AND A.SUBJECT_ID=B.SUBJECT_ID
AND A.STA_CUR=B.STA_CUR          AND A.DIM_GROUP=B.DIM_GROUP
AND A.FOR_CUR=B.FOR_CUR          AND A.ACCO_STATUS=B.ACCO_STATUS  )
WHEN MATCHED THEN
UPDATE SET  A.OB_DE_AMOUNT  =A.OB_DE_AMOUNT                 +B.OB_DE_AMOUNT
,A.OB_CR_AMOUNT  =A.OB_CR_AMOUNT                 +B.OB_CR_AMOUNT
,A.AN_DE_AMOUNT  =A.AN_DE_AMOUNT                 +B.AN_DE_AMOUNT
,A.AN_CR_AMOUNT  =A.AN_CR_AMOUNT                 +B.AN_CR_AMOUNT
,A.CB_DE_AMOUNT  =A.CB_DE_AMOUNT                 +B.CB_DE_AMOUNT
,A.CB_CR_AMOUNT  =A.CB_CR_AMOUNT                 +B.CB_CR_AMOUNT
,A.DR_AMT_TOT    =A.DR_AMT_TOT                   +B.DR_AMT_TOT
,A.CR_AMT_TOT    =A.CR_AMT_TOT                   +B.CR_AMT_TOT
,A.SE_DE_AMOUNT  =A.SE_DE_AMOUNT                 +B.SE_DE_AMOUNT
,A.SE_CR_AMOUNT  =A.SE_CR_AMOUNT                 +B.SE_CR_AMOUNT
WHEN NOT MATCHED THEN
INSERT (  BOOK_ID	,COMP_ID	,ACC_PD_ID		,SUBJECT_ID	,ITEM_CODE
,STA_CUR	,FOR_CUR	,ACCO_STATUS	,DIM_GROUP
,OB_DE_AMOUNT	,OB_CR_AMOUNT	,AN_DE_AMOUNT	,AN_CR_AMOUNT	,CB_DE_AMOUNT
,CB_CR_AMOUNT	,CR_AMT_TOT		,DR_AMT_TOT		,SE_CR_AMOUNT	,SE_DE_AMOUNT )
VALUES (B.BOOK_ID	,B.COMP_ID	,B.ACC_PD_ID	,B.SUBJECT_ID	,B.ITEM_CODE
,B.STA_CUR	,B.FOR_CUR	,B.ACCO_STATUS	,B.DIM_GROUP
,B.OB_DE_AMOUNT	,B.OB_CR_AMOUNT	,B.AN_DE_AMOUNT	,B.AN_CR_AMOUNT	,B.CB_DE_AMOUNT
,B.CB_CR_AMOUNT	,B.CR_AMT_TOT	,B.DR_AMT_TOT	,B.SE_CR_AMOUNT	,B.SE_DE_AMOUNT)';
EXECSQL(V_SQL,'', VC_PROGRAM_NAME||':18.汇总底层单位维度');
/*9.汇总底层数据到合并层,非主账簿(BOOKID<>0)数据不汇总到合并层   */
/*合并层科目汇总表 -考虑转管理范围，比如到9999的时候，科目ID要对应的转到一级科目上   */
/*9.1多管理范围*/
IF V_OWNER_COMP > 1 THEN
--EXECUTE IMMEDIATE 'CREATE TABLE FMISTMP.GT_ACC_BAL_SUM AS SELECT * FROM GT_ACC_BAL_SUM ';
--EXECUTE IMMEDIATE 'CREATE TABLE FMISTMP.GT_HZ_COMPANYINFO_SES AS SELECT * FROM GT_HZ_COMPANYINFO_SES ';
V_SQL:='MERGE INTO ACC_BAL_SUM'||I_YEAR||'_TOP A
USING (
SELECT X.ACC_PD_ID,X.BOOK_ID,T.PCOMPID AS COMP_ID,X.STA_CUR, X.FOR_CUR,X.ACCO_STATUS
,CASE WHEN T.PCOMPID = '''||V_COMPID_TOP||'''  THEN Y.SUPE_SUBJECT_ID
ELSE Y.IID END AS SUBJECT_ID     		-- 上级管理范围应取上级管理范围科目IID
,CASE WHEN T.PCOMPID = '''||V_COMPID_TOP||'''  THEN Z.ITEMCODE
ELSE Y.ITEMCODE END AS ITEM_CODE        -- 上级管理范围应取上级管理范围科目ITEMCODE
,SUM(OB_DE_AMOUNT)     AS OB_DE_AMOUNT
,SUM(OB_CR_AMOUNT)     AS OB_CR_AMOUNT
,SUM(AN_DE_AMOUNT)     AS AN_DE_AMOUNT
,SUM(AN_CR_AMOUNT)     AS AN_CR_AMOUNT
,SUM(CB_DE_AMOUNT)     AS CB_DE_AMOUNT
,SUM(CB_CR_AMOUNT)     AS CB_CR_AMOUNT
,SUM(CR_AMT_TOT  )     AS CR_AMT_TOT
,SUM(DR_AMT_TOT  )     AS DR_AMT_TOT
,SUM(SE_CR_AMOUNT)     AS SE_CR_AMOUNT
,SUM(SE_DE_AMOUNT)     AS SE_DE_AMOUNT
,SUM(X.IN_DE_OP_BAL)   AS  IN_DE_OP_BAL
,SUM(X.IN_CR_OP_BAL)   AS  IN_CR_OP_BAL
,SUM(X.IN_DE_CL_BAL)   AS  IN_DE_CL_BAL
,SUM(X.IN_CR_CL_BAL)   AS  IN_CR_CL_BAL
FROM  GT_ACC_BAL_SUM X
INNER JOIN GT_HZ_COMPANYINFO_SES T ON ( X.COMP_ID=T.COMPID  AND T.COMPID<>T.PCOMPID )
INNER JOIN T_ZWITEMSYW_EXT Y ON ( X.SUBJECT_ID=Y.IID  AND  Y.TYEAR='||V_YEAR||'  AND  Y.LEAF=1  AND  Y.COMPID='''||V_MANAGECOMPID||''' )
LEFT JOIN T_ZWITEMSYW_EXT Z ON ( Y.SUPE_SUBJECT_ID=Z.IID  AND  Z.TYEAR='||V_YEAR||'  AND  Z.COMPID='''||V_COMPID_TOP||''' AND  Z.LEAF=1)
WHERE  X.ACCO_STATUS = 1  -- 未记账数据不汇到合并层
GROUP BY  X.ACC_PD_ID,X.BOOK_ID,T.PCOMPID,X.STA_CUR, X.FOR_CUR,X.ACCO_STATUS
,CASE WHEN T.PCOMPID = '''||V_COMPID_TOP||'''  THEN Y.SUPE_SUBJECT_ID
ELSE Y.IID END
,CASE WHEN T.PCOMPID = '''||V_COMPID_TOP||'''  THEN Z.ITEMCODE
ELSE Y.ITEMCODE END
) B
ON ( A.BOOK_ID=B.BOOK_ID            AND A.COMP_ID=B.COMP_ID
AND A.ACC_PD_ID=B.ACC_PD_ID    AND A.SUBJECT_ID=B.SUBJECT_ID
AND A.STA_CUR=B.STA_CUR
AND A.FOR_CUR=B.FOR_CUR        AND A.ACCO_STATUS=B.ACCO_STATUS  )
WHEN MATCHED THEN
UPDATE SET A.OB_DE_AMOUNT  =A.OB_DE_AMOUNT                 +B.OB_DE_AMOUNT
,A.OB_CR_AMOUNT  =A.OB_CR_AMOUNT                 +B.OB_CR_AMOUNT
,A.AN_DE_AMOUNT  =A.AN_DE_AMOUNT                 +B.AN_DE_AMOUNT
,A.AN_CR_AMOUNT  =A.AN_CR_AMOUNT                 +B.AN_CR_AMOUNT
,A.CB_DE_AMOUNT  =A.CB_DE_AMOUNT                 +B.CB_DE_AMOUNT
,A.CB_CR_AMOUNT  =A.CB_CR_AMOUNT                 +B.CB_CR_AMOUNT
,A.DR_AMT_TOT    =A.DR_AMT_TOT                   +B.DR_AMT_TOT
,A.CR_AMT_TOT    =A.CR_AMT_TOT                   +B.CR_AMT_TOT
,A.SE_DE_AMOUNT  =A.SE_DE_AMOUNT                 +B.SE_DE_AMOUNT
,A.SE_CR_AMOUNT  =A.SE_CR_AMOUNT                 +B.SE_CR_AMOUNT
,A.IN_DE_OP_BAL  = CASE SIGN(B.IN_DE_OP_BAL-B.IN_CR_OP_BAL)
WHEN -1 THEN  CASE SIGN( B.IN_DE_OP_BAL - B.IN_CR_OP_BAL + B.OB_DE_AMOUNT - B.OB_CR_AMOUNT )
WHEN -1 THEN 0 WHEN 0 THEN 0
ELSE  B.IN_DE_OP_BAL - B.IN_CR_OP_BAL + B.OB_DE_AMOUNT - B.OB_CR_AMOUNT END
ELSE CASE SIGN( B.IN_DE_OP_BAL - B.IN_CR_OP_BAL + B.OB_DE_AMOUNT - B.OB_CR_AMOUNT )
WHEN 1 THEN  B.OB_DE_AMOUNT - B.OB_CR_AMOUNT
ELSE  B.IN_CR_OP_BAL - B.IN_DE_OP_BAL END   END
,A.IN_CR_OP_BAL  = CASE SIGN(B.IN_CR_OP_BAL - B.IN_DE_OP_BAL)
WHEN -1 THEN  CASE SIGN( B.IN_CR_OP_BAL - B.IN_DE_OP_BAL+B.OB_CR_AMOUNT - B.OB_DE_AMOUNT )
WHEN -1 THEN 0  WHEN 0 THEN 0
ELSE B.IN_CR_OP_BAL - B.IN_DE_OP_BAL + B.OB_CR_AMOUNT - B.OB_DE_AMOUNT END
ELSE CASE SIGN( B.IN_CR_OP_BAL - B.IN_DE_OP_BAL + B.OB_CR_AMOUNT - B.OB_DE_AMOUNT )
WHEN 1 THEN  B.OB_CR_AMOUNT - B.OB_DE_AMOUNT
ELSE  B.OB_DE_AMOUNT - B.OB_CR_AMOUNT  END   END
,A.IN_DE_CL_BAL  = CASE SIGN(B.IN_DE_CL_BAL-B.IN_CR_CL_BAL)
WHEN -1 THEN  CASE SIGN( B.IN_DE_CL_BAL-B.IN_CR_CL_BAL + B.CB_DE_AMOUNT - B.CB_CR_AMOUNT ) WHEN -1 THEN 0
WHEN 0 THEN 0
ELSE  B.IN_DE_CL_BAL - B.IN_CR_CL_BAL + B.CB_DE_AMOUNT - B.CB_CR_AMOUNT END
ELSE CASE SIGN( B.IN_DE_CL_BAL - B.IN_CR_CL_BAL  + B.CB_DE_AMOUNT - B.CB_CR_AMOUNT )
WHEN 1 THEN  B.CB_DE_AMOUNT - B.CB_CR_AMOUNT
ELSE  B.IN_CR_CL_BAL - B.IN_DE_CL_BAL END   END
,A.IN_CR_CL_BAL  = CASE SIGN(B.IN_CR_CL_BAL - B.IN_DE_CL_BAL)
WHEN -1 THEN  CASE SIGN( B.IN_CR_CL_BAL - B.IN_DE_CL_BAL+B.CB_CR_AMOUNT - B.CB_DE_AMOUNT ) WHEN -1 THEN 0
WHEN 0 THEN 0
ELSE B.IN_CR_CL_BAL - B.IN_DE_CL_BAL + B.CB_CR_AMOUNT - B.CB_DE_AMOUNT END
ELSE CASE SIGN( B.IN_CR_CL_BAL - B.IN_DE_CL_BAL + B.CB_CR_AMOUNT - B.CB_DE_AMOUNT )
WHEN 1 THEN  B.CB_CR_AMOUNT - B.CB_DE_AMOUNT
ELSE B.IN_DE_CL_BAL - B.IN_CR_CL_BAL  END   END
WHEN NOT MATCHED THEN
INSERT ( BOOK_ID	,COMP_ID	,ACC_PD_ID	,SUBJECT_ID	,STA_CUR	,FOR_CUR	,ACCO_STATUS	,ITEM_CODE
,OB_DE_AMOUNT	,OB_CR_AMOUNT	,AN_DE_AMOUNT	,AN_CR_AMOUNT	,CB_DE_AMOUNT
,CB_CR_AMOUNT	,CR_AMT_TOT		,DR_AMT_TOT		,SE_CR_AMOUNT	,SE_DE_AMOUNT
,IN_DE_OP_BAL 	,IN_CR_OP_BAL 	,IN_DE_CL_BAL 	,IN_CR_CL_BAL )
VALUES (B.BOOK_ID    ,B.COMP_ID  ,B.ACC_PD_ID  ,B.SUBJECT_ID  ,B.STA_CUR  ,B.FOR_CUR  ,B.ACCO_STATUS   ,B.ITEM_CODE
,B.OB_DE_AMOUNT ,B.OB_CR_AMOUNT  ,B.AN_DE_AMOUNT  ,B.AN_CR_AMOUNT  ,B.CB_DE_AMOUNT
,B.CB_CR_AMOUNT ,B.CR_AMT_TOT    ,B.DR_AMT_TOT  ,B.SE_CR_AMOUNT   ,B.SE_DE_AMOUNT
, CASE SIGN(B.IN_DE_OP_BAL - B.IN_CR_OP_BAL )
WHEN -1 THEN  CASE SIGN( B.IN_DE_OP_BAL - B.IN_CR_OP_BAL + B.OB_DE_AMOUNT - B.OB_CR_AMOUNT )
WHEN -1 THEN 0 WHEN 0 THEN 0
ELSE  B.IN_DE_OP_BAL - B.IN_CR_OP_BAL + B.OB_DE_AMOUNT - B.OB_CR_AMOUNT END
ELSE CASE SIGN( B.IN_DE_OP_BAL - B.IN_CR_OP_BAL + B.OB_DE_AMOUNT - B.OB_CR_AMOUNT )
WHEN 1 THEN  B.OB_DE_AMOUNT - B.OB_CR_AMOUNT
ELSE  B.IN_CR_OP_BAL - B.IN_DE_OP_BAL END   END
, CASE SIGN(B.IN_CR_OP_BAL - B.IN_DE_OP_BAL )
WHEN -1 THEN  CASE SIGN( B.IN_CR_OP_BAL - B.IN_DE_OP_BAL+B.OB_CR_AMOUNT - B.OB_DE_AMOUNT )
WHEN -1 THEN 0  WHEN 0 THEN 0
ELSE B.IN_CR_OP_BAL - B.IN_DE_OP_BAL + B.OB_CR_AMOUNT - B.OB_DE_AMOUNT END
ELSE CASE SIGN( B.IN_CR_OP_BAL - B.IN_DE_OP_BAL + B.OB_CR_AMOUNT - B.OB_DE_AMOUNT )
WHEN 1 THEN  B.OB_CR_AMOUNT - B.OB_DE_AMOUNT
ELSE  B.OB_DE_AMOUNT - B.OB_CR_AMOUNT  END   END
, CASE SIGN(B.IN_DE_CL_BAL - B.IN_CR_CL_BAL )
WHEN -1 THEN  CASE SIGN( B.IN_DE_CL_BAL-B.IN_CR_CL_BAL + B.CB_DE_AMOUNT - B.CB_CR_AMOUNT ) WHEN -1 THEN 0
WHEN 0 THEN 0
ELSE  B.IN_DE_CL_BAL - B.IN_CR_CL_BAL + B.CB_DE_AMOUNT - B.CB_CR_AMOUNT END
ELSE CASE SIGN( B.IN_DE_CL_BAL - B.IN_CR_CL_BAL  + B.CB_DE_AMOUNT - B.CB_CR_AMOUNT )
WHEN 1 THEN  B.CB_DE_AMOUNT - B.CB_CR_AMOUNT
ELSE  B.IN_CR_CL_BAL - B.IN_DE_CL_BAL END   END
, CASE SIGN(B.IN_CR_CL_BAL - B.IN_DE_CL_BAL )
WHEN -1 THEN  CASE SIGN( B.IN_CR_CL_BAL - B.IN_DE_CL_BAL+B.CB_CR_AMOUNT - B.CB_DE_AMOUNT ) WHEN -1 THEN 0
WHEN 0 THEN 0
ELSE B.IN_CR_CL_BAL - B.IN_DE_CL_BAL + B.CB_CR_AMOUNT - B.CB_DE_AMOUNT END
ELSE CASE SIGN( B.IN_CR_CL_BAL - B.IN_DE_CL_BAL + B.CB_CR_AMOUNT - B.CB_DE_AMOUNT )
WHEN 1 THEN  B.CB_CR_AMOUNT - B.CB_DE_AMOUNT
ELSE B.IN_DE_CL_BAL - B.IN_CR_CL_BAL  END   END  )';
EXECSQL(V_SQL,'', VC_PROGRAM_NAME||':19') ;
/* 合并层维度汇总表 */
V_SQL:='MERGE INTO ACC_BAL_DIM_SUM'||I_YEAR||'_TOP A
USING
( SELECT X.ACC_PD_ID,X.BOOK_ID,T.PCOMPID AS COMP_ID,X.STA_CUR, X.FOR_CUR,X.ACCO_STATUS,X.DIM_GROUP,X.ITEM_CODE
,CASE WHEN T.PCOMPID = '''||V_COMPID_TOP||'''  THEN Y.SUPE_SUBJECT_ID
ELSE X.SUBJECT_ID END AS SUBJECT_ID      -- 上级管理范围应取上级管理范围科目IID
,SUM(OB_DE_AMOUNT)     AS OB_DE_AMOUNT
,SUM(OB_CR_AMOUNT)     AS OB_CR_AMOUNT
,SUM(AN_DE_AMOUNT)     AS AN_DE_AMOUNT
,SUM(AN_CR_AMOUNT)     AS AN_CR_AMOUNT
,SUM(CB_DE_AMOUNT)     AS CB_DE_AMOUNT
,SUM(CB_CR_AMOUNT)     AS CB_CR_AMOUNT
,SUM(CR_AMT_TOT  )     AS CR_AMT_TOT
,SUM(DR_AMT_TOT  )     AS DR_AMT_TOT
,SUM(SE_CR_AMOUNT)     AS SE_CR_AMOUNT
,SUM(SE_DE_AMOUNT)     AS SE_DE_AMOUNT
FROM GT_ACC_DIM_SUM X
INNER JOIN GT_HZ_COMPANYINFO_SES T ON (X.COMP_ID=T.COMPID  AND T.COMPID<>T.PCOMPID )
LEFT JOIN T_ZWITEMSYW_EXT Y ON ( X.SUBJECT_ID=Y.IID  AND  Y.TYEAR='||V_YEAR||'  AND  Y.COMPID='''||V_COMPID_TOP||''' AND  Y.LEAF=1)
WHERE X.ACCO_STATUS = 1  -- 未记账数据不汇到合并层
GROUP BY X.ACC_PD_ID,X.BOOK_ID,T.PCOMPID,X.STA_CUR, X.FOR_CUR,X.ACCO_STATUS,X.DIM_GROUP	,X.ITEM_CODE
,CASE WHEN T.PCOMPID = '''||V_COMPID_TOP||'''  THEN Y.SUPE_SUBJECT_ID
ELSE X.SUBJECT_ID END
) B
ON (A.BOOK_ID=B.BOOK_ID            AND A.COMP_ID=B.COMP_ID
AND A.ACC_PD_ID=B.ACC_PD_ID   AND A.SUBJECT_ID=B.SUBJECT_ID
AND A.STA_CUR=B.STA_CUR        AND A.DIM_GROUP=B.DIM_GROUP
AND A.FOR_CUR=B.FOR_CUR        AND A.ACCO_STATUS=B.ACCO_STATUS  )
WHEN MATCHED THEN
UPDATE SET  A.OB_DE_AMOUNT  =A.OB_DE_AMOUNT                 +B.OB_DE_AMOUNT
,A.OB_CR_AMOUNT  =A.OB_CR_AMOUNT                 +B.OB_CR_AMOUNT
,A.AN_DE_AMOUNT  =A.AN_DE_AMOUNT                 +B.AN_DE_AMOUNT
,A.AN_CR_AMOUNT  =A.AN_CR_AMOUNT                 +B.AN_CR_AMOUNT
,A.CB_DE_AMOUNT  =A.CB_DE_AMOUNT                 +B.CB_DE_AMOUNT
,A.CB_CR_AMOUNT  =A.CB_CR_AMOUNT                 +B.CB_CR_AMOUNT
,A.DR_AMT_TOT    =A.DR_AMT_TOT                   +B.DR_AMT_TOT
,A.CR_AMT_TOT    =A.CR_AMT_TOT                   +B.CR_AMT_TOT
,A.SE_DE_AMOUNT  =A.SE_DE_AMOUNT                 +B.SE_DE_AMOUNT
,A.SE_CR_AMOUNT  =A.SE_CR_AMOUNT                 +B.SE_CR_AMOUNT
WHEN NOT MATCHED THEN
INSERT (BOOK_ID   ,COMP_ID   ,ACC_PD_ID   ,SUBJECT_ID   ,STA_CUR   ,FOR_CUR   ,ACCO_STATUS   ,DIM_GROUP
,OB_DE_AMOUNT  ,OB_CR_AMOUNT  ,AN_DE_AMOUNT,AN_CR_AMOUNT,CB_DE_AMOUNT
,CB_CR_AMOUNT  ,CR_AMT_TOT    ,DR_AMT_TOT   ,SE_CR_AMOUNT   ,SE_DE_AMOUNT)
VALUES (B.BOOK_ID       ,B.COMP_ID       ,B.ACC_PD_ID     ,B.SUBJECT_ID    ,B.STA_CUR       ,B.FOR_CUR     ,B.ACCO_STATUS  ,B.DIM_GROUP
,B.OB_DE_AMOUNT  ,B.OB_CR_AMOUNT  ,B.AN_DE_AMOUNT  ,B.AN_CR_AMOUNT  ,B.CB_DE_AMOUNT
,B.CB_CR_AMOUNT  ,B.CR_AMT_TOT    ,B.DR_AMT_TOT    ,B.SE_CR_AMOUNT  ,B.SE_DE_AMOUNT )';
EXECSQL(V_SQL,'', VC_PROGRAM_NAME||':20.合并层维度汇总表') ;
ELSE
/* 9.2单管理范围  */
V_SQL:='MERGE INTO ACC_BAL_SUM'||I_YEAR||'_TOP A
USING (
SELECT X.ACC_PD_ID		,X.BOOK_ID		,T.PCOMPID AS COMP_ID
,X.STA_CUR		,X.FOR_CUR		,X.ACCO_STATUS
,X.SUBJECT_ID          ,X.ITEM_CODE
,SUM(OB_DE_AMOUNT)     AS OB_DE_AMOUNT
,SUM(OB_CR_AMOUNT)     AS OB_CR_AMOUNT
,SUM(AN_DE_AMOUNT)     AS AN_DE_AMOUNT
,SUM(AN_CR_AMOUNT)     AS AN_CR_AMOUNT
,SUM(CB_DE_AMOUNT)     AS CB_DE_AMOUNT
,SUM(CB_CR_AMOUNT)     AS CB_CR_AMOUNT
,SUM(CR_AMT_TOT  )     AS CR_AMT_TOT
,SUM(DR_AMT_TOT  )     AS DR_AMT_TOT
,SUM(SE_CR_AMOUNT)     AS SE_CR_AMOUNT
,SUM(SE_DE_AMOUNT)     AS SE_DE_AMOUNT
,SUM(X.IN_DE_OP_BAL)   AS  IN_DE_OP_BAL
,SUM(X.IN_CR_OP_BAL)   AS  IN_CR_OP_BAL
,SUM(X.IN_DE_CL_BAL)   AS  IN_DE_CL_BAL
,SUM(X.IN_CR_CL_BAL)   AS  IN_CR_CL_BAL
FROM  GT_ACC_BAL_SUM X
INNER JOIN GT_HZ_COMPANYINFO_SES T ON ( X.COMP_ID=T.COMPID  AND T.COMPID<>T.PCOMPID )
WHERE X.ACCO_STATUS = 1  -- 未记账数据不汇到合并层
GROUP BY  X.ACC_PD_ID,X.BOOK_ID,T.PCOMPID
,X.STA_CUR, X.FOR_CUR,X.ACCO_STATUS
,X.SUBJECT_ID         ,X.ITEM_CODE
) B
ON (A.BOOK_ID=B.BOOK_ID            AND A.COMP_ID=B.COMP_ID
AND A.ACC_PD_ID=B.ACC_PD_ID    AND A.SUBJECT_ID=B.SUBJECT_ID
AND A.STA_CUR=B.STA_CUR  AND A.FOR_CUR=B.FOR_CUR        AND A.ACCO_STATUS=B.ACCO_STATUS )
WHEN MATCHED THEN
UPDATE SET A.OB_DE_AMOUNT  =A.OB_DE_AMOUNT                 +B.OB_DE_AMOUNT
,A.OB_CR_AMOUNT  =A.OB_CR_AMOUNT                 +B.OB_CR_AMOUNT
,A.AN_DE_AMOUNT  =A.AN_DE_AMOUNT                 +B.AN_DE_AMOUNT
,A.AN_CR_AMOUNT  =A.AN_CR_AMOUNT                 +B.AN_CR_AMOUNT
,A.CB_DE_AMOUNT  =A.CB_DE_AMOUNT                 +B.CB_DE_AMOUNT
,A.CB_CR_AMOUNT  =A.CB_CR_AMOUNT                 +B.CB_CR_AMOUNT
,A.DR_AMT_TOT    =A.DR_AMT_TOT                   +B.DR_AMT_TOT
,A.CR_AMT_TOT    =A.CR_AMT_TOT                   +B.CR_AMT_TOT
,A.SE_DE_AMOUNT  =A.SE_DE_AMOUNT                 +B.SE_DE_AMOUNT
,A.SE_CR_AMOUNT  =A.SE_CR_AMOUNT                 +B.SE_CR_AMOUNT
,A.IN_DE_OP_BAL  = CASE SIGN(B.IN_DE_OP_BAL-B.IN_CR_OP_BAL)
WHEN -1 THEN  CASE SIGN( B.IN_DE_OP_BAL - B.IN_CR_OP_BAL + B.OB_DE_AMOUNT - B.OB_CR_AMOUNT )
WHEN -1 THEN 0 WHEN 0 THEN 0
ELSE  B.IN_DE_OP_BAL - B.IN_CR_OP_BAL + B.OB_DE_AMOUNT - B.OB_CR_AMOUNT END
ELSE CASE SIGN( B.IN_DE_OP_BAL - B.IN_CR_OP_BAL + B.OB_DE_AMOUNT - B.OB_CR_AMOUNT )
WHEN 1 THEN  B.OB_DE_AMOUNT - B.OB_CR_AMOUNT
ELSE  B.IN_CR_OP_BAL - B.IN_DE_OP_BAL END   END
,A.IN_CR_OP_BAL  = CASE SIGN(B.IN_CR_OP_BAL - B.IN_DE_OP_BAL)
WHEN -1 THEN  CASE SIGN( B.IN_CR_OP_BAL - B.IN_DE_OP_BAL+B.OB_CR_AMOUNT - B.OB_DE_AMOUNT )
WHEN -1 THEN 0  WHEN 0 THEN 0
ELSE B.IN_CR_OP_BAL - B.IN_DE_OP_BAL + B.OB_CR_AMOUNT - B.OB_DE_AMOUNT END
ELSE CASE SIGN( B.IN_CR_OP_BAL - B.IN_DE_OP_BAL + B.OB_CR_AMOUNT - B.OB_DE_AMOUNT )
WHEN 1 THEN  B.OB_CR_AMOUNT - B.OB_DE_AMOUNT
ELSE  B.OB_DE_AMOUNT - B.OB_CR_AMOUNT  END   END
,A.IN_DE_CL_BAL  = CASE SIGN(B.IN_DE_CL_BAL-B.IN_CR_CL_BAL)
WHEN -1 THEN  CASE SIGN( B.IN_DE_CL_BAL-B.IN_CR_CL_BAL + B.CB_DE_AMOUNT - B.CB_CR_AMOUNT ) WHEN -1 THEN 0
WHEN 0 THEN 0
ELSE  B.IN_DE_CL_BAL - B.IN_CR_CL_BAL + B.CB_DE_AMOUNT - B.CB_CR_AMOUNT END
ELSE CASE SIGN( B.IN_DE_CL_BAL - B.IN_CR_CL_BAL  + B.CB_DE_AMOUNT - B.CB_CR_AMOUNT )
WHEN 1 THEN  B.CB_DE_AMOUNT - B.CB_CR_AMOUNT
ELSE  B.IN_CR_CL_BAL - B.IN_DE_CL_BAL END   END
,A.IN_CR_CL_BAL  = CASE SIGN(B.IN_CR_CL_BAL - B.IN_DE_CL_BAL)
WHEN -1 THEN  CASE SIGN( B.IN_CR_CL_BAL - B.IN_DE_CL_BAL+B.CB_CR_AMOUNT - B.CB_DE_AMOUNT ) WHEN -1 THEN 0
WHEN 0 THEN 0
ELSE B.IN_CR_CL_BAL - B.IN_DE_CL_BAL + B.CB_CR_AMOUNT - B.CB_DE_AMOUNT END
ELSE CASE SIGN( B.IN_CR_CL_BAL - B.IN_DE_CL_BAL + B.CB_CR_AMOUNT - B.CB_DE_AMOUNT )
WHEN 1 THEN  B.CB_CR_AMOUNT - B.CB_DE_AMOUNT
ELSE B.IN_DE_CL_BAL - B.IN_CR_CL_BAL  END   END
WHEN NOT MATCHED THEN
INSERT (  BOOK_ID	,COMP_ID	,ACC_PD_ID		,SUBJECT_ID
,STA_CUR	,FOR_CUR	,ACCO_STATUS	,ITEM_CODE
,OB_DE_AMOUNT	,OB_CR_AMOUNT	,AN_DE_AMOUNT	,AN_CR_AMOUNT	,CB_DE_AMOUNT
,CB_CR_AMOUNT	,CR_AMT_TOT		,DR_AMT_TOT		,SE_CR_AMOUNT	,SE_DE_AMOUNT
,IN_DE_OP_BAL 	,IN_CR_OP_BAL 	,IN_DE_CL_BAL 	,IN_CR_CL_BAL )
VALUES (B.BOOK_ID    ,B.COMP_ID  ,B.ACC_PD_ID  ,B.SUBJECT_ID  ,B.STA_CUR  ,B.FOR_CUR  ,B.ACCO_STATUS   ,B.ITEM_CODE
,B.OB_DE_AMOUNT ,B.OB_CR_AMOUNT  ,B.AN_DE_AMOUNT  ,B.AN_CR_AMOUNT  ,B.CB_DE_AMOUNT
,B.CB_CR_AMOUNT ,B.CR_AMT_TOT    ,B.DR_AMT_TOT  ,B.SE_CR_AMOUNT   ,B.SE_DE_AMOUNT
, CASE SIGN(B.IN_DE_OP_BAL - B.IN_CR_OP_BAL )
WHEN -1 THEN  CASE SIGN( B.IN_DE_OP_BAL - B.IN_CR_OP_BAL + B.OB_DE_AMOUNT - B.OB_CR_AMOUNT )
WHEN -1 THEN 0 WHEN 0 THEN 0
ELSE  B.IN_DE_OP_BAL - B.IN_CR_OP_BAL + B.OB_DE_AMOUNT - B.OB_CR_AMOUNT END
ELSE CASE SIGN( B.IN_DE_OP_BAL - B.IN_CR_OP_BAL + B.OB_DE_AMOUNT - B.OB_CR_AMOUNT )
WHEN 1 THEN  B.OB_DE_AMOUNT - B.OB_CR_AMOUNT
ELSE  B.IN_CR_OP_BAL - B.IN_DE_OP_BAL END   END
, CASE SIGN(B.IN_CR_OP_BAL - B.IN_DE_OP_BAL )
WHEN -1 THEN  CASE SIGN( B.IN_CR_OP_BAL - B.IN_DE_OP_BAL+B.OB_CR_AMOUNT - B.OB_DE_AMOUNT )
WHEN -1 THEN 0  WHEN 0 THEN 0
ELSE B.IN_CR_OP_BAL - B.IN_DE_OP_BAL + B.OB_CR_AMOUNT - B.OB_DE_AMOUNT END
ELSE CASE SIGN( B.IN_CR_OP_BAL - B.IN_DE_OP_BAL + B.OB_CR_AMOUNT - B.OB_DE_AMOUNT )
WHEN 1 THEN  B.OB_CR_AMOUNT - B.OB_DE_AMOUNT
ELSE  B.OB_DE_AMOUNT - B.OB_CR_AMOUNT  END   END
, CASE SIGN(B.IN_DE_CL_BAL - B.IN_CR_CL_BAL )
WHEN -1 THEN  CASE SIGN( B.IN_DE_CL_BAL-B.IN_CR_CL_BAL + B.CB_DE_AMOUNT - B.CB_CR_AMOUNT ) WHEN -1 THEN 0
WHEN 0 THEN 0
ELSE  B.IN_DE_CL_BAL - B.IN_CR_CL_BAL + B.CB_DE_AMOUNT - B.CB_CR_AMOUNT END
ELSE CASE SIGN( B.IN_DE_CL_BAL - B.IN_CR_CL_BAL  + B.CB_DE_AMOUNT - B.CB_CR_AMOUNT )
WHEN 1 THEN  B.CB_DE_AMOUNT - B.CB_CR_AMOUNT
ELSE  B.IN_CR_CL_BAL - B.IN_DE_CL_BAL END   END
, CASE SIGN(B.IN_CR_CL_BAL - B.IN_DE_CL_BAL )
WHEN -1 THEN  CASE SIGN( B.IN_CR_CL_BAL - B.IN_DE_CL_BAL+B.CB_CR_AMOUNT - B.CB_DE_AMOUNT ) WHEN -1 THEN 0
WHEN 0 THEN 0
ELSE B.IN_CR_CL_BAL - B.IN_DE_CL_BAL + B.CB_CR_AMOUNT - B.CB_DE_AMOUNT END
ELSE CASE SIGN( B.IN_CR_CL_BAL - B.IN_DE_CL_BAL + B.CB_CR_AMOUNT - B.CB_DE_AMOUNT )
WHEN 1 THEN  B.CB_CR_AMOUNT - B.CB_DE_AMOUNT
ELSE B.IN_DE_CL_BAL - B.IN_CR_CL_BAL  END   END  )';
EXECSQL(V_SQL,'', VC_PROGRAM_NAME||':21.合并层科目汇总表') ;
/* 合并层维度汇总表 */
V_SQL:='MERGE INTO ACC_BAL_DIM_SUM'||I_YEAR||'_TOP A
USING
( SELECT X.ACC_PD_ID	,X.BOOK_ID		,T.PCOMPID AS COMP_ID	,X.STA_CUR	,X.ITEM_CODE
,X.FOR_CUR	,X.ACCO_STATUS	,X.DIM_GROUP 	,X.SUBJECT_ID
,SUM(OB_DE_AMOUNT)     AS OB_DE_AMOUNT
,SUM(OB_CR_AMOUNT)     AS OB_CR_AMOUNT
,SUM(AN_DE_AMOUNT)     AS AN_DE_AMOUNT
,SUM(AN_CR_AMOUNT)     AS AN_CR_AMOUNT
,SUM(CB_DE_AMOUNT)     AS CB_DE_AMOUNT
,SUM(CB_CR_AMOUNT)     AS CB_CR_AMOUNT
,SUM(CR_AMT_TOT  )     AS CR_AMT_TOT
,SUM(DR_AMT_TOT  )     AS DR_AMT_TOT
,SUM(SE_CR_AMOUNT)     AS SE_CR_AMOUNT
,SUM(SE_DE_AMOUNT)     AS SE_DE_AMOUNT
FROM GT_ACC_DIM_SUM X
,GT_HZ_COMPANYINFO_SES T
WHERE  X.COMP_ID=T.COMPID   AND T.COMPID<>T.PCOMPID AND X.ACCO_STATUS = 1  -- 未记账数据不汇到合并层
GROUP BY X.ACC_PD_ID,X.BOOK_ID,T.PCOMPID,X.STA_CUR,X.ITEM_CODE
, X.FOR_CUR,X.ACCO_STATUS,X.DIM_GROUP,X.SUBJECT_ID
) B
ON (A.BOOK_ID=B.BOOK_ID            AND A.COMP_ID=B.COMP_ID
AND A.ACC_PD_ID=B.ACC_PD_ID   AND A.SUBJECT_ID=B.SUBJECT_ID
AND A.STA_CUR=B.STA_CUR        AND A.DIM_GROUP=B.DIM_GROUP
AND A.FOR_CUR=B.FOR_CUR        AND A.ACCO_STATUS=B.ACCO_STATUS  )
WHEN MATCHED THEN
UPDATE SET  A.OB_DE_AMOUNT  =A.OB_DE_AMOUNT                 +B.OB_DE_AMOUNT
,A.OB_CR_AMOUNT  =A.OB_CR_AMOUNT                 +B.OB_CR_AMOUNT
,A.AN_DE_AMOUNT  =A.AN_DE_AMOUNT                 +B.AN_DE_AMOUNT
,A.AN_CR_AMOUNT  =A.AN_CR_AMOUNT                 +B.AN_CR_AMOUNT
,A.CB_DE_AMOUNT  =A.CB_DE_AMOUNT                 +B.CB_DE_AMOUNT
,A.CB_CR_AMOUNT  =A.CB_CR_AMOUNT                 +B.CB_CR_AMOUNT
,A.DR_AMT_TOT    =A.DR_AMT_TOT                   +B.DR_AMT_TOT
,A.CR_AMT_TOT    =A.CR_AMT_TOT                   +B.CR_AMT_TOT
,A.SE_DE_AMOUNT  =A.SE_DE_AMOUNT                 +B.SE_DE_AMOUNT
,A.SE_CR_AMOUNT  =A.SE_CR_AMOUNT                 +B.SE_CR_AMOUNT
WHEN NOT MATCHED THEN
INSERT (	BOOK_ID   ,COMP_ID   ,ACC_PD_ID   ,SUBJECT_ID   ,ITEM_CODE
,STA_CUR   ,FOR_CUR   ,ACCO_STATUS   ,DIM_GROUP
,OB_DE_AMOUNT  ,OB_CR_AMOUNT  ,AN_DE_AMOUNT,AN_CR_AMOUNT,CB_DE_AMOUNT
,CB_CR_AMOUNT  ,CR_AMT_TOT    ,DR_AMT_TOT   ,SE_CR_AMOUNT   ,SE_DE_AMOUNT)
VALUES (B.BOOK_ID       ,B.COMP_ID       ,B.ACC_PD_ID     ,B.SUBJECT_ID    ,B.ITEM_CODE
,B.STA_CUR       ,B.FOR_CUR     ,B.ACCO_STATUS  ,B.DIM_GROUP
,B.OB_DE_AMOUNT  ,B.OB_CR_AMOUNT  ,B.AN_DE_AMOUNT  ,B.AN_CR_AMOUNT  ,B.CB_DE_AMOUNT
,B.CB_CR_AMOUNT  ,B.CR_AMT_TOT    ,B.DR_AMT_TOT    ,B.SE_CR_AMOUNT  ,B.SE_DE_AMOUNT )';
EXECSQL(V_SQL,'', VC_PROGRAM_NAME||':22.合并层维度汇总表') ;
END IF ;
/*10.更新单位行维度汇总表 */
/*底层单位行维度汇总表 */
V_SQL:='INSERT INTO GT_ACC_DIM_SUM_EXT
(BOOK_ID		,COMP_ID		,ACC_PD_ID
,SUBJECT_ID		,ACCO_STATUS	,STA_CUR
,FOR_CUR		,DIM_GROUP		,DIM_CLS
,DIM_TYPE_ID	,DIM_VALUE		,ITEM_CODE
,OB_DE_AMOUNT	,OB_CR_AMOUNT	,AN_DE_AMOUNT	,AN_CR_AMOUNT
,CB_DE_AMOUNT	,CB_CR_AMOUNT	,CR_AMT_TOT		,DR_AMT_TOT
,SE_CR_AMOUNT	,SE_DE_AMOUNT 	 )
SELECT
X.BOOK_ID    	,X.COMP_ID		,X.ACC_PD_ID
,X.SUBJECT_ID 	,X.ACCO_STATUS  ,X.STA_CUR
,X.FOR_CUR      ,X.DIM_GROUP  	,K.DIM_CLS
,K.DIM_TYPE_ID  ,K.DIM_VALUE   	,Y.ITEMCODE
,SUM(OB_DE_AMOUNT)     AS OB_DE_AMOUNT
,SUM(OB_CR_AMOUNT)     AS OB_CR_AMOUNT
,SUM(AN_DE_AMOUNT)     AS AN_DE_AMOUNT
,SUM(AN_CR_AMOUNT)     AS AN_CR_AMOUNT
,SUM(CB_DE_AMOUNT)     AS CB_DE_AMOUNT
,SUM(CB_CR_AMOUNT)     AS CB_CR_AMOUNT
,SUM(CR_AMT_TOT  )     AS CR_AMT_TOT
,SUM(DR_AMT_TOT  )     AS DR_AMT_TOT
,SUM(SE_CR_AMOUNT)     AS SE_CR_AMOUNT
,SUM(SE_DE_AMOUNT)     AS SE_DE_AMOUNT
FROM GT_ACC_DIM_SUM X    -- SUM表有些ITEMCODE没值，所以需要关联科目表
INNER JOIN ACC_DIM_GROUP K   ON ( X.DIM_GROUP=K.DIM_GROUP )
INNER JOIN T_ZWITEMSYW_EXT Y ON ( X.SUBJECT_ID=Y.IID  AND  Y.TYEAR='||V_YEAR||'  AND  Y.LEAF=1  AND  Y.COMPID='''||V_MANAGECOMPID||''' )
GROUP BY X.BOOK_ID    	,X.COMP_ID		,X.ACC_PD_ID
,X.SUBJECT_ID 	,X.ACCO_STATUS  ,X.STA_CUR
,X.FOR_CUR      ,X.DIM_GROUP  	,K.DIM_CLS
,K.DIM_TYPE_ID  ,K.DIM_VALUE   	,Y.ITEMCODE    ' ;
EXECSQL(V_SQL,'', VC_PROGRAM_NAME||':23.汇总数据到临时表') ;
--EXECUTE IMMEDIATE 'CREATE TABLE GT_ACC_DIM_SUM_EXT_BAK AS SELECT * FROM GT_ACC_DIM_SUM_EXT ';
V_SQL:='MERGE /*+ ORDER(B,A), USE_NL_WITH_INDEX(B,A)*/ INTO ACC_BAL_DIM_ROW'||I_YEAR||' A
USING GT_ACC_DIM_SUM_EXT B
ON (  A.BOOK_ID=B.BOOK_ID           AND A.COMP_ID=B.COMP_ID   AND A.ACC_PD_ID=B.ACC_PD_ID
AND A.SUBJECT_ID=B.SUBJECT_ID   AND A.STA_CUR=B.STA_CUR
AND A.FOR_CUR=B.FOR_CUR         AND A.ACCO_STATUS=B.ACCO_STATUS
AND A.DIM_GROUP=B.DIM_GROUP     AND A.DIM_CLS=B.DIM_CLS
AND A.DIM_TYPE_ID=B.DIM_TYPE_ID AND A.DIM_VALUE=B.DIM_VALUE  )
WHEN MATCHED THEN
UPDATE SET  A.OB_DE_AMOUNT  = A.OB_DE_AMOUNT                 +B.OB_DE_AMOUNT
,A.OB_CR_AMOUNT  = A.OB_CR_AMOUNT                 +B.OB_CR_AMOUNT
,A.AN_DE_AMOUNT  = A.AN_DE_AMOUNT                 +B.AN_DE_AMOUNT
,A.AN_CR_AMOUNT  = A.AN_CR_AMOUNT                 +B.AN_CR_AMOUNT
,A.CB_DE_AMOUNT  = A.CB_DE_AMOUNT                 +B.CB_DE_AMOUNT
,A.CB_CR_AMOUNT  = A.CB_CR_AMOUNT                 +B.CB_CR_AMOUNT
,A.DR_AMT_TOT    = A.DR_AMT_TOT                   +B.DR_AMT_TOT
,A.CR_AMT_TOT    = A.CR_AMT_TOT                   +B.CR_AMT_TOT
,A.SE_DE_AMOUNT  = A.SE_DE_AMOUNT                 +B.SE_DE_AMOUNT
,A.SE_CR_AMOUNT  = A.SE_CR_AMOUNT                 +B.SE_CR_AMOUNT
WHEN NOT MATCHED THEN
INSERT (BOOK_ID,COMP_ID,ACC_PD_ID,SUBJECT_ID,STA_CUR,FOR_CUR,ACCO_STATUS,DIM_GROUP,DIM_CLS,DIM_TYPE_ID,DIM_VALUE
,OB_DE_AMOUNT,OB_CR_AMOUNT,AN_DE_AMOUNT,AN_CR_AMOUNT  ,ITEM_CODE
,CB_DE_AMOUNT,CB_CR_AMOUNT,CR_AMT_TOT,DR_AMT_TOT,SE_CR_AMOUNT,SE_DE_AMOUNT )
VALUES (B.BOOK_ID		,B.COMP_ID	,B.ACC_PD_ID	,B.SUBJECT_ID	,B.STA_CUR	,B.FOR_CUR
,B.ACCO_STATUS	,B.DIM_GROUP	,B.DIM_CLS		,B.DIM_TYPE_ID	 ,B.DIM_VALUE
,B.OB_DE_AMOUNT	,B.OB_CR_AMOUNT	,B.AN_DE_AMOUNT	,B.AN_CR_AMOUNT  ,B.ITEM_CODE
,B.CB_DE_AMOUNT	,B.CB_CR_AMOUNT	,B.CR_AMT_TOT	,B.DR_AMT_TOT	 ,B.SE_CR_AMOUNT	,B.SE_DE_AMOUNT ) ';
EXECSQL(V_SQL,'', VC_PROGRAM_NAME||':24.底层单位行维度');
/*合并层行维度汇总表 */
IF V_OWNER_COMP > 1 THEN
/*多管理范围*/
EXECUTE IMMEDIATE 'TRUNCATE TABLE GT_ACC_DIM_SUM_EXT ';
V_SQL:='INSERT INTO GT_ACC_DIM_SUM_EXT
(BOOK_ID
,COMP_ID
,ACC_PD_ID
,SUBJECT_ID
,ACCO_STATUS
,STA_CUR
,FOR_CUR
,DIM_GROUP
,DIM_CLS
,DIM_TYPE_ID
,DIM_VALUE
,ITEM_CODE
,OB_DE_AMOUNT
,OB_CR_AMOUNT
,AN_DE_AMOUNT
,AN_CR_AMOUNT
,CB_DE_AMOUNT
,CB_CR_AMOUNT
,CR_AMT_TOT
,DR_AMT_TOT
,SE_CR_AMOUNT
,SE_DE_AMOUNT )
SELECT    X.BOOK_ID    	,T.PCOMPID	,X.ACC_PD_ID
,CASE WHEN T.PCOMPID = '''||V_COMPID_TOP||'''  THEN Y.SUPE_SUBJECT_ID
ELSE Y.IID END AS SUBJECT_ID     		-- 上级管理范围应取上级管理范围科目IID
,X.ACCO_STATUS  ,X.STA_CUR 	,X.FOR_CUR
,X.DIM_GROUP  	,K.DIM_CLS  ,K.DIM_TYPE_ID  ,K.DIM_VALUE
,CASE WHEN T.PCOMPID = '''||V_COMPID_TOP||'''  THEN Z.ITEMCODE
ELSE Y.ITEMCODE END AS ITEM_CODE        -- 上级管理范围应取上级管理范围科目ITEMCODE
,SUM(OB_DE_AMOUNT)     AS OB_DE_AMOUNT
,SUM(OB_CR_AMOUNT)     AS OB_CR_AMOUNT
,SUM(AN_DE_AMOUNT)     AS AN_DE_AMOUNT
,SUM(AN_CR_AMOUNT)     AS AN_CR_AMOUNT
,SUM(CB_DE_AMOUNT)     AS CB_DE_AMOUNT
,SUM(CB_CR_AMOUNT)     AS CB_CR_AMOUNT
,SUM(CR_AMT_TOT  )     AS CR_AMT_TOT
,SUM(DR_AMT_TOT  )     AS DR_AMT_TOT
,SUM(SE_CR_AMOUNT)     AS SE_CR_AMOUNT
,SUM(SE_DE_AMOUNT)     AS SE_DE_AMOUNT
FROM GT_ACC_DIM_SUM X
INNER JOIN ACC_DIM_GROUP K   ON ( X.DIM_GROUP=K.DIM_GROUP )
INNER JOIN GT_HZ_COMPANYINFO_SES T ON ( X.COMP_ID=T.COMPID   AND T.COMPID<>T.PCOMPID)
INNER JOIN T_ZWITEMSYW_EXT Y ON ( X.SUBJECT_ID=Y.IID  AND  Y.TYEAR='||V_YEAR||'  AND  Y.LEAF=1  AND  Y.COMPID='''||V_MANAGECOMPID||''' )
LEFT  JOIN T_ZWITEMSYW_EXT Z ON ( Y.SUPE_SUBJECT_ID=Z.IID  AND  Z.TYEAR='||V_YEAR||' AND Z.LEAF=1  AND  Z.COMPID='''||V_COMPID_TOP||''' )
WHERE X.ACCO_STATUS = 1  -- 未记账数据不汇到合并层
GROUP BY X.BOOK_ID    	,T.PCOMPID		,X.ACC_PD_ID
,CASE WHEN T.PCOMPID = '''||V_COMPID_TOP||'''  THEN Y.SUPE_SUBJECT_ID
ELSE Y.IID END
,X.ACCO_STATUS  ,X.STA_CUR 	,X.FOR_CUR
,X.DIM_GROUP  	,K.DIM_CLS  ,K.DIM_TYPE_ID  ,K.DIM_VALUE
,CASE WHEN T.PCOMPID = '''||V_COMPID_TOP||'''  THEN Z.ITEMCODE
ELSE Y.ITEMCODE END   ';
EXECSQL(V_SQL,'', VC_PROGRAM_NAME||':25.汇总数据到临时表') ;
V_SQL:='MERGE /*+ ORDER(B,A) , USE_HASH(A,B), NO_USE_CVT_VAR*/ INTO ACC_BAL_DIM_ROW'||I_YEAR||'_TOP A
USING GT_ACC_DIM_SUM_EXT B
ON ( A.BOOK_ID=B.BOOK_ID                AND A.COMP_ID=B.COMP_ID
AND A.ACC_PD_ID=B.ACC_PD_ID        AND A.SUBJECT_ID=B.SUBJECT_ID
AND A.STA_CUR=B.STA_CUR            AND A.DIM_CLS=B.DIM_CLS
AND A.FOR_CUR=B.FOR_CUR            AND A.ACCO_STATUS=B.ACCO_STATUS    AND A.DIM_GROUP=B.DIM_GROUP
AND A.DIM_TYPE_ID=B.DIM_TYPE_ID    AND A.DIM_VALUE=B.DIM_VALUE )
WHEN MATCHED THEN
UPDATE SET   A.OB_DE_AMOUNT  =A.OB_DE_AMOUNT           +B.OB_DE_AMOUNT
,A.OB_CR_AMOUNT  =A.OB_CR_AMOUNT                 +B.OB_CR_AMOUNT
,A.AN_DE_AMOUNT  =A.AN_DE_AMOUNT                 +B.AN_DE_AMOUNT
,A.AN_CR_AMOUNT  =A.AN_CR_AMOUNT                 +B.AN_CR_AMOUNT
,A.CB_DE_AMOUNT  =A.CB_DE_AMOUNT                 +B.CB_DE_AMOUNT
,A.CB_CR_AMOUNT  =A.CB_CR_AMOUNT                 +B.CB_CR_AMOUNT
,A.DR_AMT_TOT    =A.DR_AMT_TOT                   +B.DR_AMT_TOT
,A.CR_AMT_TOT    =A.CR_AMT_TOT                   +B.CR_AMT_TOT
,A.SE_DE_AMOUNT  =A.SE_DE_AMOUNT                 +B.SE_DE_AMOUNT
,A.SE_CR_AMOUNT  =A.SE_CR_AMOUNT                 +B.SE_CR_AMOUNT
WHEN NOT MATCHED THEN
INSERT ( BOOK_ID,COMP_ID,ACC_PD_ID,SUBJECT_ID,STA_CUR,FOR_CUR,ACCO_STATUS,DIM_GROUP,DIM_CLS,DIM_TYPE_ID,DIM_VALUE
,OB_DE_AMOUNT,OB_CR_AMOUNT,AN_DE_AMOUNT,AN_CR_AMOUNT,CB_DE_AMOUNT,CB_CR_AMOUNT
,CR_AMT_TOT,DR_AMT_TOT,SE_CR_AMOUNT,SE_DE_AMOUNT , ITEM_CODE )
VALUES (B.BOOK_ID		,B.COMP_ID			,B.ACC_PD_ID	,B.SUBJECT_ID		,B.STA_CUR
,B.FOR_CUR		,B.ACCO_STATUS		,B.DIM_GROUP	,B.DIM_CLS			,B.DIM_TYPE_ID		,B.DIM_VALUE
,B.OB_DE_AMOUNT	,B.OB_CR_AMOUNT		,B.AN_DE_AMOUNT	,B.AN_CR_AMOUNT		,B.CB_DE_AMOUNT		,B.CB_CR_AMOUNT
,B.CR_AMT_TOT	,B.DR_AMT_TOT		,B.SE_CR_AMOUNT	,B.SE_DE_AMOUNT 	,B.ITEM_CODE )';
EXECSQL(V_SQL,'', VC_PROGRAM_NAME||':26.合并层行维度');
ELSE /*单管理范围*/
EXECUTE IMMEDIATE 'TRUNCATE TABLE GT_ACC_DIM_SUM_EXT ';
V_SQL:='INSERT INTO GT_ACC_DIM_SUM_EXT
( BOOK_ID
,COMP_ID
,ACC_PD_ID
,SUBJECT_ID
,ACCO_STATUS
,STA_CUR
,FOR_CUR
,DIM_GROUP
,DIM_CLS
,DIM_TYPE_ID
,DIM_VALUE
,ITEM_CODE
,OB_DE_AMOUNT
,OB_CR_AMOUNT
,AN_DE_AMOUNT
,AN_CR_AMOUNT
,CB_DE_AMOUNT
,CB_CR_AMOUNT
,CR_AMT_TOT
,DR_AMT_TOT
,SE_CR_AMOUNT
,SE_DE_AMOUNT   )
SELECT    X.BOOK_ID    	,T.PCOMPID		,X.ACC_PD_ID
,X.SUBJECT_ID	,X.ACCO_STATUS  ,X.STA_CUR
,X.FOR_CUR    	,X.DIM_GROUP  	,K.DIM_CLS
,K.DIM_TYPE_ID  ,K.DIM_VALUE 	,Y.ITEMCODE
,SUM(OB_DE_AMOUNT)     AS OB_DE_AMOUNT
,SUM(OB_CR_AMOUNT)     AS OB_CR_AMOUNT
,SUM(AN_DE_AMOUNT)     AS AN_DE_AMOUNT
,SUM(AN_CR_AMOUNT)     AS AN_CR_AMOUNT
,SUM(CB_DE_AMOUNT)     AS CB_DE_AMOUNT
,SUM(CB_CR_AMOUNT)     AS CB_CR_AMOUNT
,SUM(CR_AMT_TOT  )     AS CR_AMT_TOT
,SUM(DR_AMT_TOT  )     AS DR_AMT_TOT
,SUM(SE_CR_AMOUNT)     AS SE_CR_AMOUNT
,SUM(SE_DE_AMOUNT)     AS SE_DE_AMOUNT
FROM GT_ACC_DIM_SUM X
INNER JOIN ACC_DIM_GROUP K   ON ( X.DIM_GROUP=K.DIM_GROUP )
INNER JOIN GT_HZ_COMPANYINFO_SES T ON ( X.COMP_ID=T.COMPID   AND T.COMPID<>T.PCOMPID)
INNER JOIN T_ZWITEMSYW_EXT Y ON ( X.SUBJECT_ID=Y.IID  AND  Y.TYEAR='||V_YEAR||'  AND  Y.LEAF=1  AND  Y.COMPID='''||V_MANAGECOMPID||''' )
WHERE X.ACCO_STATUS = 1  -- 未记账数据不汇到合并层
GROUP BY X.BOOK_ID    	,T.PCOMPID		,X.ACC_PD_ID
,X.SUBJECT_ID	,X.ACCO_STATUS  ,X.STA_CUR
,X.FOR_CUR    	,X.DIM_GROUP  	,K.DIM_CLS
,K.DIM_TYPE_ID  ,K.DIM_VALUE 	,Y.ITEMCODE  ';
EXECSQL(V_SQL,'', VC_PROGRAM_NAME||':27.汇总数据') ;
V_SQL:='MERGE /*+ ORDER(B,A), USE_NL_WITH_INDEX(B,A)*/ INTO ACC_BAL_DIM_ROW'||I_YEAR||'_TOP A
USING GT_ACC_DIM_SUM_EXT  B
ON (A.BOOK_ID=B.BOOK_ID                AND A.COMP_ID=B.COMP_ID
AND A.ACC_PD_ID=B.ACC_PD_ID      AND A.SUBJECT_ID=B.SUBJECT_ID
AND A.STA_CUR=B.STA_CUR           AND A.DIM_CLS=B.DIM_CLS
AND A.FOR_CUR=B.FOR_CUR           AND A.ACCO_STATUS=B.ACCO_STATUS    AND A.DIM_GROUP=B.DIM_GROUP
AND A.DIM_TYPE_ID=B.DIM_TYPE_ID   AND A.DIM_VALUE=B.DIM_VALUE )
WHEN MATCHED THEN
UPDATE SET   A.OB_DE_AMOUNT  =A.OB_DE_AMOUNT           +B.OB_DE_AMOUNT
,A.OB_CR_AMOUNT  =A.OB_CR_AMOUNT                 +B.OB_CR_AMOUNT
,A.AN_DE_AMOUNT  =A.AN_DE_AMOUNT                 +B.AN_DE_AMOUNT
,A.AN_CR_AMOUNT  =A.AN_CR_AMOUNT                 +B.AN_CR_AMOUNT
,A.CB_DE_AMOUNT  =A.CB_DE_AMOUNT                 +B.CB_DE_AMOUNT
,A.CB_CR_AMOUNT  =A.CB_CR_AMOUNT                 +B.CB_CR_AMOUNT
,A.DR_AMT_TOT    =A.DR_AMT_TOT                   +B.DR_AMT_TOT
,A.CR_AMT_TOT    =A.CR_AMT_TOT                   +B.CR_AMT_TOT
,A.SE_DE_AMOUNT  =A.SE_DE_AMOUNT                 +B.SE_DE_AMOUNT
,A.SE_CR_AMOUNT  =A.SE_CR_AMOUNT                 +B.SE_CR_AMOUNT
WHEN NOT MATCHED THEN
INSERT ( BOOK_ID ,COMP_ID ,ACC_PD_ID ,SUBJECT_ID ,STA_CUR ,FOR_CUR ,ACCO_STATUS ,DIM_GROUP
,DIM_CLS ,DIM_TYPE_ID ,DIM_VALUE
,OB_DE_AMOUNT ,OB_CR_AMOUNT ,AN_DE_AMOUNT ,AN_CR_AMOUNT ,CB_DE_AMOUNT ,CB_CR_AMOUNT
,CR_AMT_TOT 	,DR_AMT_TOT ,SE_CR_AMOUNT ,SE_DE_AMOUNT , ITEM_CODE )
VALUES (B.BOOK_ID 		,B.COMP_ID 		,B.ACC_PD_ID 	,B.SUBJECT_ID 	,B.STA_CUR ,B.FOR_CUR
,B.ACCO_STATUS  ,B.DIM_GROUP  	,B.DIM_CLS  	,B.DIM_TYPE_ID  ,B.DIM_VALUE
,B.OB_DE_AMOUNT ,B.OB_CR_AMOUNT ,B.AN_DE_AMOUNT ,B.AN_CR_AMOUNT ,B.CB_DE_AMOUNT 	,B.CB_CR_AMOUNT
,B.CR_AMT_TOT 	,B.DR_AMT_TOT 	,B.SE_CR_AMOUNT ,B.SE_DE_AMOUNT , B.ITEM_CODE )';
EXECSQL(V_SQL,'', VC_PROGRAM_NAME||':28.合并层行维度汇总表') ;
END IF;
/*11.如果凭证年份不等于本年，需更新后面年份数据,非主账簿(BOOKID<>0)数据不汇总到合并层  */
IF V_YEAR<TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')) THEN
FOR XYEAR IN V_YEAR+1 .. TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')) LOOP
/*11.1.更新V_YEAR+1及以后年份的底层单位科目汇总表 */
V_SQL:= 'MERGE  INTO ACC_BAL_SUM'||TO_CHAR(XYEAR)||' A
USING (
SELECT X.BOOK_ID,X.COMP_ID,X.SUBJECT_ID,X.STA_CUR, X.FOR_CUR,X.ACCO_STATUS,
SUM(X.CB_DE_AMOUNT) AS SUM_J_QM,    --本期借方发生金额
SUM(X.CB_CR_AMOUNT) AS SUM_D_QM     --本期贷方发生金额
FROM GT_ACC_BAL_SUM X,T_ZWITEMSYW_EXT Y
WHERE X.SUBJECT_ID=Y.IID AND Y.TYEAR='||XYEAR||' AND Y.LEAF=1 AND Y.COMPID='''||V_MANAGECOMPID||'''
AND SUBSTR(ACC_PD_ID,5,4) = ''1202''
GROUP BY X.BOOK_ID,X.COMP_ID,X.SUBJECT_ID,X.STA_CUR, X.FOR_CUR,X.ACCO_STATUS  ) B
ON ( A.BOOK_ID=B.BOOK_ID               AND A.COMP_ID=B.COMP_ID
AND A.SUBJECT_ID=B.SUBJECT_ID     AND A.STA_CUR=B.STA_CUR
AND A.FOR_CUR=B.FOR_CUR           AND A.ACCO_STATUS=B.ACCO_STATUS )
WHEN MATCHED THEN
UPDATE SET A.OB_DE_AMOUNT=A.OB_DE_AMOUNT+B.SUM_J_QM
,A.OB_CR_AMOUNT=A.OB_CR_AMOUNT+B.SUM_D_QM
,A.CB_DE_AMOUNT=A.CB_DE_AMOUNT+B.SUM_J_QM
,A.CB_CR_AMOUNT=A.CB_CR_AMOUNT+B.SUM_D_QM ';
EXECSQL(V_SQL,'', VC_PROGRAM_NAME||':更新V_YEAR+1及以后年份1');
/*11.2.更新V_YEAR+1及以后年份的底层单位维度科目汇总表 */
V_SQL:='MERGE INTO ACC_BAL_DIM_SUM'||TO_CHAR(XYEAR)||' A
USING (
SELECT X.BOOK_ID,X.COMP_ID,X.SUBJECT_ID,X.STA_CUR, X.FOR_CUR,X.ACCO_STATUS,X.DIM_GROUP,
SUM(X.CB_DE_AMOUNT) AS SUM_J_QM,    --本期借方发生金额
SUM(X.CB_CR_AMOUNT) AS SUM_D_QM     --本期贷方发生金额
FROM GT_ACC_DIM_SUM X,T_ZWITEMSYW_EXT Y
WHERE X.SUBJECT_ID=Y.IID AND Y.TYEAR='||XYEAR||' AND Y.LEAF=1 AND Y.COMPID='''||V_MANAGECOMPID||'''
AND SUBSTR(ACC_PD_ID,5,4) = ''1202''
GROUP BY X.BOOK_ID,X.COMP_ID,X.SUBJECT_ID,X.STA_CUR, X.FOR_CUR,X.ACCO_STATUS,X.DIM_GROUP  ) B
ON (A.BOOK_ID=B.BOOK_ID              AND A.COMP_ID=B.COMP_ID                AND A.DIM_GROUP=B.DIM_GROUP
AND A.SUBJECT_ID=B.SUBJECT_ID   AND A.STA_CUR=B.STA_CUR
AND A.FOR_CUR=B.FOR_CUR         AND A.ACCO_STATUS=B.ACCO_STATUS  )
WHEN MATCHED THEN
UPDATE SET A.OB_DE_AMOUNT=A.OB_DE_AMOUNT+B.SUM_J_QM
,A.OB_CR_AMOUNT=A.OB_CR_AMOUNT+B.SUM_D_QM
,A.CB_DE_AMOUNT=A.CB_DE_AMOUNT+B.SUM_J_QM
,A.CB_CR_AMOUNT=A.CB_CR_AMOUNT+B.SUM_D_QM  ';
EXECSQL(V_SQL,'', VC_PROGRAM_NAME||':更新V_YEAR+1及以后年份2');
/*11.3.更新V_YEAR+1及以后年份的合并层科目汇总表 */
V_SQL:='MERGE INTO ACC_BAL_SUM'||TO_CHAR(XYEAR)||'_TOP A
USING (
SELECT X.BOOK_ID,T.PCOMPID AS COMP_ID,X.SUBJECT_ID,X.STA_CUR, X.FOR_CUR,X.ACCO_STATUS,
SUM(X.CB_DE_AMOUNT) AS SUM_J_QM,    --本期借方发生金额
SUM(X.CB_CR_AMOUNT) AS SUM_D_QM     --本期贷方发生金额
FROM GT_ACC_BAL_SUM X,T_ZWITEMSYW_EXT Y ,GT_HZ_COMPANYINFO_SES T
WHERE X.SUBJECT_ID=Y.IID AND Y.TYEAR='||TO_CHAR(XYEAR)||' AND Y.LEAF=1 AND Y.COMPID='''||V_MANAGECOMPID||'''
AND X.BOOK_ID=0  AND SUBSTR(ACC_PD_ID,5,4) = ''1202''  AND T.COMPID<>T.PCOMPID
AND X.ACCO_STATUS = 1  -- 未记账数据不汇到合并层
GROUP BY X.BOOK_ID,T.PCOMPID,X.SUBJECT_ID,X.STA_CUR, X.FOR_CUR,X.ACCO_STATUS  ) B
ON ( A.BOOK_ID=B.BOOK_ID             AND A.COMP_ID=B.COMP_ID
AND A.SUBJECT_ID=B.SUBJECT_ID   AND A.STA_CUR=B.STA_CUR
AND A.FOR_CUR=B.FOR_CUR         AND A.ACCO_STATUS=B.ACCO_STATUS )
WHEN MATCHED THEN
UPDATE SET A.OB_DE_AMOUNT=A.OB_DE_AMOUNT+B.SUM_J_QM
,A.OB_CR_AMOUNT=A.OB_CR_AMOUNT+B.SUM_D_QM
,A.CB_DE_AMOUNT=A.CB_DE_AMOUNT+B.SUM_J_QM
,A.CB_CR_AMOUNT=A.CB_CR_AMOUNT+B.SUM_D_QM  ' ;
EXECSQL(V_SQL,'', VC_PROGRAM_NAME||':更新V_YEAR+1及以后年份3');
/*11.4.更新V_YEAR+1及以后年份的合并层维度科目汇总表 */
V_SQL:='MERGE  INTO ACC_BAL_DIM_SUM'||TO_CHAR(XYEAR)||'_TOP A
USING (
SELECT X.BOOK_ID,T.PCOMPID AS COMP_ID,X.SUBJECT_ID,X.STA_CUR, X.FOR_CUR,X.ACCO_STATUS,X.DIM_GROUP,
SUM(X.CB_DE_AMOUNT) AS SUM_J_QM,    --本期借方发生金额
SUM(X.CB_CR_AMOUNT) AS SUM_D_QM     --本期贷方发生金额
FROM GT_ACC_DIM_SUM X,T_ZWITEMSYW_EXT Y ,GT_HZ_COMPANYINFO_SES T
WHERE X.SUBJECT_ID=Y.IID AND Y.TYEAR='||TO_CHAR(XYEAR)||' AND Y.LEAF=1 AND Y.COMPID='''||V_MANAGECOMPID||'''
AND X.BOOK_ID=0  AND SUBSTR(ACC_PD_ID,5,4) = ''1202''  AND T.COMPID<>T.PCOMPID
AND X.ACCO_STATUS = 1  -- 未记账数据不汇到合并层
GROUP BY X.BOOK_ID,T.PCOMPID ,X.SUBJECT_ID,X.STA_CUR, X.FOR_CUR,X.ACCO_STATUS,X.DIM_GROUP  ) B
ON (  A.BOOK_ID=B.BOOK_ID             AND A.COMP_ID=B.COMP_ID       AND A.DIM_GROUP=B.DIM_GROUP
AND A.SUBJECT_ID=B.SUBJECT_ID   AND A.STA_CUR=B.STA_CUR
AND A.FOR_CUR=B.FOR_CUR         AND A.ACCO_STATUS=B.ACCO_STATUS  )
WHEN MATCHED THEN
UPDATE SET A.OB_DE_AMOUNT=A.OB_DE_AMOUNT+B.SUM_J_QM
,A.OB_CR_AMOUNT=A.OB_CR_AMOUNT+B.SUM_D_QM
,A.CB_DE_AMOUNT=A.CB_DE_AMOUNT+B.SUM_J_QM
,A.CB_CR_AMOUNT=A.CB_CR_AMOUNT+B.SUM_D_QM  ' ;
EXECSQL(V_SQL,'', VC_PROGRAM_NAME||':更新V_YEAR+1及以后年份4');
/*11.5.更新V_YEAR+1及以后年份的底层单位行维度科目汇总表 */
V_SQL:='MERGE  INTO ACC_BAL_DIM_ROW'||TO_CHAR(XYEAR)||' A
USING (
SELECT X.BOOK_ID,X.COMP_ID,X.SUBJECT_ID,X.STA_CUR, X.FOR_CUR,X.ACCO_STATUS,X.DIM_GROUP,K.DIM_CLS,K.DIM_TYPE_ID,K.DIM_VALUE,
SUM(X.CB_DE_AMOUNT) AS SUM_J_QM,    --本期借方发生金额
SUM(X.CB_CR_AMOUNT) AS SUM_D_QM     --本期贷方发生金额
FROM GT_ACC_DIM_SUM X,T_ZWITEMSYW_EXT Y ,ACC_DIM_GROUP K
WHERE X.SUBJECT_ID=Y.IID AND Y.TYEAR='||TO_CHAR(XYEAR)||' AND Y.LEAF=1
AND Y.COMPID='''||V_MANAGECOMPID||''' AND X.DIM_GROUP=K.DIM_GROUP
AND SUBSTR(ACC_PD_ID,5,4) = ''1202''
GROUP BY X.BOOK_ID,X.COMP_ID,X.SUBJECT_ID,X.STA_CUR, X.FOR_CUR,X.ACCO_STATUS,X.DIM_GROUP,K.DIM_CLS,K.DIM_TYPE_ID,K.DIM_VALUE  ) B
ON (  A.BOOK_ID=B.BOOK_ID               AND A.COMP_ID=B.COMP_ID
AND A.SUBJECT_ID=B.SUBJECT_ID     AND A.STA_CUR=B.STA_CUR
AND A.FOR_CUR=B.FOR_CUR           AND A.ACCO_STATUS=B.ACCO_STATUS
AND A.DIM_GROUP=B.DIM_GROUP       AND A.DIM_CLS=B.DIM_CLS
AND A.DIM_TYPE_ID=B.DIM_TYPE_ID   AND A.DIM_VALUE=B.DIM_VALUE  )
WHEN MATCHED THEN
UPDATE SET A.OB_DE_AMOUNT=A.OB_DE_AMOUNT+B.SUM_J_QM
,A.OB_CR_AMOUNT=A.OB_CR_AMOUNT+B.SUM_D_QM
,A.CB_DE_AMOUNT=A.CB_DE_AMOUNT+B.SUM_J_QM
,A.CB_CR_AMOUNT=A.CB_CR_AMOUNT+B.SUM_D_QM ' ;
EXECSQL(V_SQL,'', VC_PROGRAM_NAME||':更新V_YEAR+1及以后年份5');
/*11.6.更新V_YEAR+1及以后年份的合并层行维度汇总表 */
V_SQL:='MERGE  INTO ACC_BAL_DIM_ROW'||TO_CHAR(XYEAR)||'_TOP A
USING (
SELECT X.BOOK_ID,T.PCOMPID AS COMP_ID,X.SUBJECT_ID,X.STA_CUR, X.FOR_CUR,X.ACCO_STATUS,X.DIM_GROUP,K.DIM_CLS,K.DIM_TYPE_ID,K.DIM_VALUE,
SUM(X.CB_DE_AMOUNT) AS SUM_J_QM,    --本期借方发生金额
SUM(X.CB_CR_AMOUNT) AS SUM_D_QM     --本期贷方发生金额
FROM GT_ACC_DIM_SUM X,T_ZWITEMSYW_EXT Y ,GT_HZ_COMPANYINFO_SES T,ACC_DIM_GROUP K
WHERE X.SUBJECT_ID=Y.IID AND Y.TYEAR='||TO_CHAR(XYEAR)||' AND Y.LEAF=1
AND Y.COMPID='''||V_MANAGECOMPID||''' AND X.DIM_GROUP=K.DIM_GROUP AND X.BOOK_ID=0
AND SUBSTR(ACC_PD_ID,5,4) = ''1202''  AND T.COMPID<>T.PCOMPID
AND X.ACCO_STATUS = 1  -- 未记账数据不汇到合并层
GROUP BY X.BOOK_ID,T.PCOMPID ,X.SUBJECT_ID,X.STA_CUR, X.FOR_CUR,X.ACCO_STATUS,X.DIM_GROUP,K.DIM_CLS,K.DIM_TYPE_ID,K.DIM_VALUE  ) B
ON (  A.BOOK_ID=B.BOOK_ID               AND A.COMP_ID=B.COMP_ID
AND A.SUBJECT_ID=B.SUBJECT_ID     AND A.STA_CUR=B.STA_CUR
AND A.FOR_CUR=B.FOR_CUR           AND A.ACCO_STATUS=B.ACCO_STATUS
AND A.DIM_GROUP=B.DIM_GROUP       AND A.DIM_CLS=B.DIM_CLS
AND A.DIM_TYPE_ID=B.DIM_TYPE_ID   AND A.DIM_VALUE=B.DIM_VALUE  )
WHEN MATCHED THEN
UPDATE SET A.OB_DE_AMOUNT=A.OB_DE_AMOUNT+B.SUM_J_QM
,A.OB_CR_AMOUNT=A.OB_CR_AMOUNT+B.SUM_D_QM
,A.CB_DE_AMOUNT=A.CB_DE_AMOUNT+B.SUM_J_QM
,A.CB_CR_AMOUNT=A.CB_CR_AMOUNT+B.SUM_D_QM  ';
EXECSQL(V_SQL,'', VC_PROGRAM_NAME||':更新V_YEAR+1及以后年份6');
END LOOP;
END IF;
--12.更新取数公式表
UPDATE  ACC_COLANDFETCH_TIME SET COLL_LAST_TIME=SYSDATE
WHERE  ( COMP_ID,ACC_PD_ID  ) IN (
SELECT T.PCOMPID,X.ACC_PD_ID  FROM GT_ACC_BAL_SUM X
INNER JOIN GT_HZ_COMPANYINFO_SES T ON ( X.COMP_ID=T.COMPID  )
GROUP BY T.PCOMPID,X.ACC_PD_ID  ) ;
VPD_PKG.SET_VPD_STATE_ENABLE();
VD_END_TIME:=SYSDATE;
INSERT INTO HZ_LOG
(LOG_NO ,USER_NAME ,BEGIN_TIME ,END_TIME ,LOG_LEVEL ,LOG_TYPE ,LOG_DETAIL ,IP_ADDRESS ,TIMES ,TIME_DD)
VALUES (HZ_SEQ.NEXTVAL,VC_CURRENT_SCHEMA,VD_BEGIN_TIME,VD_END_TIME,'INFO', VC_PROGRAM_NAME,'凭证实时汇总执行成功:'||I_BILLID
,SYS_CONTEXT('USERENV','IP_ADDRESS'),ROUND((VD_END_TIME-VD_BEGIN_TIME)*86400,2), TO_NUMBER(TO_CHAR(VD_BEGIN_TIME,'DD')));
COMMIT;
/*
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
VPD_PKG.SET_VPD_STATE_ENABLE();
VD_END_TIME:=SYSDATE;
INSERT INTO HZ_LOG
(LOG_NO ,USER_NAME ,BEGIN_TIME ,END_TIME ,LOG_LEVEL ,LOG_TYPE ,LOG_DETAIL ,IP_ADDRESS ,TIMES ,TIME_DD,SQL_TEXT)
VALUES (HZ_SEQ.NEXTVAL,VC_CURRENT_SCHEMA,VD_BEGIN_TIME,VD_END_TIME,'INFO', VC_PROGRAM_NAME
,'凭证增量汇总执行失败('||I_BILLID||'):'||DBMS_UTILITY.FORMAT_ERROR_STACK
,SYS_CONTEXT('USERENV','IP_ADDRESS'),ROUND((VD_END_TIME-VD_BEGIN_TIME)*86400,2), TO_NUMBER(TO_CHAR(VD_BEGIN_TIME,'DD')),V_SQL);
COMMIT;
RAISE;  */
END PR_ACC_ONE_BILL_IN;